Application.WorksheetFunction.Match problem

[Carl Brehm]

Why does the following work, unless Vendor_Number ends in a 0?
1 works but 100 does not
Leaving Vendor_Row = 0 when there is a 100 to match.

Could there be a formatting problem?
All Range("ven_no") is formatted as Text


Sub post_vendor_totals()
Dim Vendor_row As Double
Dim Vendor_Number As Integer
Dim VEN_ROW As Double
Dim column_num As String

Vendor_Number = Application.InputBox("Enter Vendor Number", "Vendor Number", , , , , , 1)
column_num = Sheets("sheet1").Range("i3").Value

On Error Resume Next
Vendor_row = Application.WorksheetFunction.Match(Vendor_Number, Range("ven_no"), 0)
On Error GoTo 0
If Vendor_row = 0 Then
Exit Sub
Else
Vendor_row = Vendor_row + 2
Sheets("sheet2").Range("b" & Vendor_row).Value = Sheets("sheet1").Range("d1").Value
End If

more code..

Thanks
Carl Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores

 

[Bob Phillips]

Try declaring Vendor_Number as string

Dim Vendor_Number As String

--

HTH

RP
(remove nothere from the email address if mailing direct)


Why does the following work, unless Vendor_Number ends in a 0?
1 works but 100 does not
Leaving Vendor_Row = 0 when there is a 100 to match.

Could there be a formatting problem?
All Range("ven_no") is formatted as Text


Sub post_vendor_totals()
Dim Vendor_row As Double
Dim Vendor_Number As Integer
Dim VEN_ROW As Double
Dim column_num As String

Vendor_Number = Application.InputBox("Enter Vendor Number", "Vendor Number", , , , , , 1)
column_num = Sheets("sheet1").Range("i3").Value

On Error Resume Next
Vendor_row = Application.WorksheetFunction.Match(Vendor_Number, Range("ven_no"), 0)
On Error GoTo 0
If Vendor_row = 0 Then
Exit Sub
Else
Vendor_row = Vendor_row + 2
Sheets("sheet2").Range("b" & Vendor_row).Value = Sheets("sheet1").Range("d1").Value
End If

more code..

Thanks
Carl Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores