This is the code I am using for the OK button on a user form.
The named range is "catalog" and the sheet it was named from is Item Catalog.
The vlookup line is supposed to take what was transferred from the dialog box into the first column, look it up in the named range Catalog and return that to the second column on the same line.
Sub cmdOK_Click()
Dim NextRow As Long
Dim inumber As Long
Dim ws As Worksheet
Dim rItem As Range
' Activate the worksheet
Sheets("Estimate of Quantities").Activate
'Determine the next empty row
NextRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer New Item Number and Quantity
Cells(NextRow, 1).Value = txtItemNo.Value
Cells(NextRow, 3).Value = txtQty.Value
Set rItem = Worksheets("Estimate of Quantities").Cells(NextRow, 1).CurrentRegion
With rItem
.Cells(NextRow, 1).Resize(.Rows.Count - 2, .Columns.Count).Font.Name = "Calibri"
.Cells(NextRow, 1).Resize(.Rows.Count - 2, .Columns.Count).Font.Size = 11
End With
'Use vlookup to get description from named range
Sheets("Item Catalog").Activate
inumber = Cells(NextRow, 1)
Set ws = Sheets("ITEM CATALOG")
Result = Application.VLookup(inumber, ws.Range("Catalog"), 3, False)
'Trap errors
If IsError(Result) Then
MsgBox "No Match Found, Enter a Correct Number"
Else
Cells(NextRow, 2) = Result
End If
'Clear contents for next entry
txtItemNo.Text = ""
txtQty.Text = ""
txtItemNo.SetFocus
End Sub
The transfer to the Item Number column and Quantity column works well. The VLOOKUP does not . It always skips to the error trap.
What am I doing wrong.? Please help.
The named range is "catalog" and the sheet it was named from is Item Catalog.
The vlookup line is supposed to take what was transferred from the dialog box into the first column, look it up in the named range Catalog and return that to the second column on the same line.
Sub cmdOK_Click()
Dim NextRow As Long
Dim inumber As Long
Dim ws As Worksheet
Dim rItem As Range
' Activate the worksheet
Sheets("Estimate of Quantities").Activate
'Determine the next empty row
NextRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer New Item Number and Quantity
Cells(NextRow, 1).Value = txtItemNo.Value
Cells(NextRow, 3).Value = txtQty.Value
Set rItem = Worksheets("Estimate of Quantities").Cells(NextRow, 1).CurrentRegion
With rItem
.Cells(NextRow, 1).Resize(.Rows.Count - 2, .Columns.Count).Font.Name = "Calibri"
.Cells(NextRow, 1).Resize(.Rows.Count - 2, .Columns.Count).Font.Size = 11
End With
'Use vlookup to get description from named range
Sheets("Item Catalog").Activate
inumber = Cells(NextRow, 1)
Set ws = Sheets("ITEM CATALOG")
Result = Application.VLookup(inumber, ws.Range("Catalog"), 3, False)
'Trap errors
If IsError(Result) Then
MsgBox "No Match Found, Enter a Correct Number"
Else
Cells(NextRow, 2) = Result
End If
'Clear contents for next entry
txtItemNo.Text = ""
txtQty.Text = ""
txtItemNo.SetFocus
End Sub
The transfer to the Item Number column and Quantity column works well. The VLOOKUP does not . It always skips to the error trap.
What am I doing wrong.? Please help.