Hi guys,

i'm news to VBA.

below coding's purpose is to find a match string in a table by using a For loop.

My problem is that i always get error at this step "sh2.Range("A" & matchRow).Value = SupplierCbx.Value"

I have put some msgbox to monitor data such as "lastrow","matchrow". Result is lastrow = 16(correct), matchrow = 0 (when i run application automatically after open excel file). however, sometime, i get correct matchrow result when just run the specific userform.

'Update Data
'find match data
Dim index As Integer
Dim lastRow As Integer

Dim sh2 As Worksheet
Set sh2 = ThisWorkbook.Sheets("ItemsMasterPage")

lastRow = sh2.Range("B10000").End(xlUp).Row
For index = 2 To lastRow
If Cells(index, 2) = SupplierPartTxt.Value Then
matchRow = index
End If
Next index

sh2.Range("A" & matchRow).Value = SupplierCbx.Value
sh2.Range("B" & matchRow).Value = SupplierPartTxt.Value
sh2.Range("C" & matchRow).Value = DescriptionTxt.Value
sh2.Range("D" & matchRow).Value = UnitCbx.Value
sh2.Range("E" & matchRow).Value = CategoryCbx.Value
sh2.Range("F" & matchRow).Value = PricePerPcsTxt.Value
sh2.Range("G" & matchRow).Value = ReorderQtyTxt.Value
sh2.Range("H" & matchRow).Value = StorageCbx.Value

Msgbox "components has updated"