VBA: ទាយយកទិន្នន័យចុងក្រោយនៃតារាងក្នុង Excel

MS Excel: Get contents of last cell in a range that has data in Excel 2010/2007/2003/XP/2000/97

Question: In Microsoft Excel 2010/2007/2003/XP/2000/97, how do I make a cell display the contents of the last cell of a range that has data?

Answer: This is a bit tricky and the formula that you need to use will depend on the types of data that you have in the range.

Data range is formatted as text

If your range contains only text values, you could use the following formula:

=INDEX(range,MATCH(REPT("z",255),range))

where range is the range that you wish to check

For example, if you wanted to find the last text value in column A, you would use the following formula:

=INDEX(A:A,MATCH(REPT("z",255),A:A))

For example, if you wanted to find the last text value in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MATCH(REPT("z",255),C2:C10))

Data range is formatted as numeric

If your range contains only numbers, you could use the following formula:

=INDEX(range,MATCH(9.99999999999999E+307,range))

where range is the range that you wish to check

For example, if you wanted to find the last numeric value in column A, you would use the following formula:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

For example, if you wanted to find the last numeric value in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MATCH(9.99999999999999E+307,C2:C10))

Data range contains both text and numeric

If your range contains both text and numeric values (ie: there must be at least one text and one numeric value in your range) and you want to find the last value, you could use the following formula:

=INDEX(range,MAX(MATCH(9.99999999999999E+307,range),MATCH(REPT("z",255),range)))

where range if the range that you wish to check

For example, if you wanted to find the last value (either text or numeric) in column A, you would use the following formula:

=INDEX(A:A,MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",255),A:A)))

For example, if you wanted to find the last value (either text or numeric) in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MAX(MATCH(9.99999999999999E+307,C2:C10),MATCH(REPT("z",255),C2:C10)))

ចុចទីនេះអានបន្ត>>>

VBA: បញ្ចូលទិន្នន័យពីតារាងមួយទៅតារាងមួយ

Private Sub Cmdadd_Click()
On Error Resume Next
Dim sht As Worksheet
Dim rdata
Application.ScreenUpdating = False
If MsgBox("Save date to Mater Date" & " " & Sheet2.Range("").Value, vbYesNo) = vbYes Then
Sheet2.PrintOut
Set rdata = Sheet3.Cells(65536, 1).End(xlUp).Offset(1, 0)
With rdata

.Offset(0, 4) = Sheet2.Range("C7").Value 'Code Vendor
.Offset(0, 5) = Sheet2.Range("I7").Value 'Code Department
.Offset(0, 6) = Sheet2.Range("I8").Value 'Code Outlet
.Offset(0, 7) = Sheet2.Range("I9").Value 'Code Date Required
.Offset(0, 8) = Sheet2.Range("B12").Value 'Code Date Requesed

End With
Worksheets("sheet3").Save
Application.ScreenUpdating = True
ActiveWorkbook.Save
Else
Exit Sub
End If
End Sub

ចុចទីនេះអានបន្ត>>>

VBA: Call ប្រើសម្រាប់ហៅ Macro ឲ្យដំណើរការ

Sub CmdOpen_Click()

Call OpenSheet

End Sub

• OpenSheet ជាឈ្មោះ Marco ដែលត្រូវដំណើរការពេល Marco CmdOpen_Click

ចុចទីនេះអានបន្ត>>>

VBA: ដំណើរការកូដពេលបើកឯកសារ Excel

Private Sub Workbook_Open()

Run "GetData"

End Sub

• GetData ជាឈ្មោះ Macro ដែលដំណើរការពេលបើកឯកសារ

ចុចទីនេះអានបន្ត>>>

VBA: ចម្លងទិន្នន័យពីឯកសារ Excel ដែលមិនដំណើរការដាក់ក្នុងឯកសារថ្មីមួយទៀត

Sub GetDataFromClosedFille()
Dim mydata As String
'data location & range to copy
mydata = "='D:\Inventory\[Jan.xlsm]Monthly'!$A$1:$R$144" '<< change as required

'link to worksheet
With ThisWorkbook.Worksheets("Jan").Range("A1:R144") '<< change as required
.Formula = mydata

'convert formula to text
.Value = .Value
End With
End Sub

• D:\Inventory\ ទីតាំងទុកឯកសារ
• [Jan.xlsm] ឈ្មោះឯកសារត្រូវដាក់ក្នុង [...]
• Monthly ឈ្មោះ Sheet 
• $A$1:$R$144 ទីតាំង Cell ក្នុង sheet ខាងលើ
• Jan ជាឈ្មោះ sheet ក្នុង Workbook ថ្មី ដែលត្រូវ Copy ពី sheet Monthly នៃ Workbook Jan
• A1:R144 ទីតាំង Cell ក្នុង sheet Jan ដែលត្រូវ Copy ពី $A$1:$R$144 នៃ sheet Monthly

ចុចទីនេះអានបន្ត>>>