I need help with this code. Driving me crazy!!

khodjo

Board Regular
Joined
Mar 24, 2006
Messages
51
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I think you want to get inumber before you switch sheets:

Code:
'Use vlookup to get description from named range
[COLOR="Green"]Sheets("Item Catalog").Activate[/COLOR]
[COLOR="Blue"]inumber = Cells(NextRow, 1)[/COLOR]
Set ws = Sheets("ITEM CATALOG")
Result = Application.VLookup(inumber, ws.Range("Catalog"), 3, False)

Should be:
Code:
'Use vlookup to get description from named range
[COLOR="blue"]inumber = Cells(NextRow, 1)[/COLOR]
[COLOR="green"]Sheets("Item Catalog").Activate[/COLOR]
Set ws = Sheets("ITEM CATALOG")
Result = Application.VLookup(inumber, ws.Range("Catalog"), 3, False)

The Cells() reference takes the cell from the active sheet - once you switch to the Item Catalog tab, it's looking on that sheet ...
 
Upvote 0
Try changing Dim inumber As Long to As Range and then this
Code:
inumber = Cells(NextRow, 1)
change for
Code:
Set inumber = Range("A" & NextRow)
 
Upvote 0
I haven't tested this at all but rather than VLOOKUP you could maybe use the VBA Find method:

Code:
Sub cmdOK_Click()
Dim NextRow As Long
Dim rItem As Range
Dim findRange 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

' do lookup using Find Method
Set findRange = Range("Catalog").Find(txtItemNo.Value, LookIn:=xlValues)
If Not findRange Is Nothing Then
    Cells(NextRow, 2) = findRange.Offset(0, 3)
Else
    MsgBox "No Match Found, Enter a Correct Number"
    
End If
'Clear contents for next entry
txtItemNo.Text = ""
txtQty.Text = ""
txtItemNo.SetFocus
End Sub

Dom
 
Upvote 0
Thanks for the help. I have it working now. I am going to try and improve it some more.
Thanks again everyone.
 
Upvote 0

Forum statistics

Threads
1,215,914
Messages
6,127,690
Members
449,398
Latest member
m_a_advisoryforall

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top