Vlookup error

O

OOC

I am using the below program to iterate rows and calculate pipe weights based
on several values. When my "A" variable exceeds "4", my answer returns "0".
Any size 4 and below on the A variable works perfectly. I have checked my
tables and ensured that they were numbers and declared them as strings in the
code. I ahve stepped into the code and it functions fine until it exceeds
the value. Anyone with any ideas? I am going CRAZY!



'This code is for calculating weight of pipe according to size, schedule,
type and length.

Sub Weight1()
'Declare Variables
Dim Length As String
Dim Answer As String
Dim A As String
'
'Declare a counter to iterate through each line of the piping spreadsheet
and calculate each line.
'Set length to zero if no length is entered on spreadsheet.
'Set Answer variable to zero between iterations.
Dim Counter1 As Integer
For Counter1 = 8 To 34
Length = Worksheets("Piping").Cells(Counter1, 11).Value
A = Worksheets("Piping").Cells(Counter1, 6).Value
If Length = "" Then Length = 0
Answer = 0
'
'The following statements sort the Appendix according to schedule and pipe
type and then
'calculates weight.
If Worksheets("Piping").Cells(Counter1, 7).Value = 10 Then
If Worksheets("Piping").Cells(Counter1, 10).Value = "A106B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 2) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "A53 Gr B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 3) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "304" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 4) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "216" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 5) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "321" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 6) * Length
ElseIf Not IsEmpty(Worksheets("Piping").Cells(Counter1, 10).Value) Then
MsgBox ("Check Material Type")
End If
ElseIf Worksheets("Piping").Cells(Counter1, 7).Value = 40 Then
If Worksheets("Piping").Cells(Counter1, 10).Value = "A106B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 2) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "A53 Gr B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 3) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "304" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 4) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "216" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 5) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "321" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 6) * Length
ElseIf Not IsEmpty(Worksheets("Piping").Cells(Counter1, 10).Value) Then
MsgBox ("Check Material Type")
End If
ElseIf Worksheets("Piping").Cells(Counter1, 7).Value = 80 Then
If Worksheets("Piping").Cells(Counter1, 10).Value = "A106B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("O2:T23"), 2) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "A53 Gr B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("O2:T23"), 3) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "304" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("O2:T23"), 4) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "216" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("O2:T23"), 5) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "321" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("O2:T23"), 6) * Length
ElseIf Not IsEmpty(Worksheets("Piping").Cells(Counter1, 10).Value) Then
MsgBox ("Check Material Type")
End If
ElseIf Worksheets("Piping").Cells(Counter1, 7).Value = 120 Then
If Worksheets("Piping").Cells(Counter1, 10).Value = "A106B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("V2:AA23"), 2) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "A53 Gr B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("V2:AA23"), 3) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "304" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("V2:AA23"), 4) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "216" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("V2:AA23"), 5) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "321" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("V2:AA23"), 6) * Length
ElseIf Not IsEmpty(Worksheets("Piping").Cells(Counter1, 10).Value) Then
MsgBox ("Check Material Type")
End If
ElseIf Worksheets("Piping").Cells(Counter1, 7).Value = 160 Then
If Worksheets("Piping").Cells(Counter1, 10).Value = "A106B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("AC2:AH23"), 2) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "A53 Gr B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("AC2:AH23"), 3) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "304" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("AC2:AH23"), 4) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "216" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("AC2:AH23"), 5) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "321" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("AC2:AH23"), 6) * Length
ElseIf Not IsEmpty(Worksheets("Piping").Cells(Counter1, 10).Value) Then
MsgBox ("Check Material Type")
End If
ElseIf Not IsEmpty(Worksheets("Piping").Cells(Counter1, 7).Value) Then
MsgBox ("Check Pipe Schedule")
End If
Worksheets("Piping").Cells(Counter1, 12).Value = Answer
Next Counter1
End Sub
 
T

Tom Ogilvy

Your search term variable "A" is dimensioned as String.

generally, "123" <> 123 when using match or lookup functions. I suspect
this is the root cause of your problem. Since you are using a form of
vlookup that depends on ordered data, make sure your data is ordered for a
textual look up.

set up a vlookup formula in the worksheet and get it working first.
 
O

OOC

Thanks Tom. I went back and found that even though you format the cell as a
number in Excel 2003 that it retains its original formatting unless you
reenter the number. Excel also shows a system note note in the upper left
corner of the cell as a note to remind you that the contents is being
considered text. By highlighting all the cells and clicking on the note, you
then have the option to convert the contents to number format. When I
changed the formatting using this method, my problems were solved. Thanks
again!
 
N

NickHK

Not sure, but doesn't this simplify to the code below, or something similar
?
Untested and requires error trapping and checks.

Note the change to the variables that are used as numeric.
Not sure about "A", as that depends on your data.

Sub Weight1()
Dim Codes() As String
Dim Length As Double
Dim Answer As Double
Dim A As String
Dim Counter1 As Integer
Dim SearchRange As Range
Dim OffsetVal As Long

'Or get these codes from a WS Range
Const AllCodes As String = "A106B,A53 Gr B,304,216,321"
Codes = Split(AllCodes, ",")

For Counter1 = 8 To 34
With Worksheets("Piping").Cells(Counter1, 7)
Length = CDbl(.Offset(0, 4).Value)
A = .Offset(0, -1).Value

Set SearchRange = Worksheets("Appendix A").Range("A2").Offset(0,
(.Value \ 40) * 7).Resize(22, 6)

OffsetVal = Application.WorksheetFunction.Match(.Offset(0, 3).Value,
Codes, 0)

.Offset(Counter1, 5).Value = Excel.WorksheetFunction.VLookup(A,
SearchRange, OffsetVal) * Length

End With
Next Counter1

End Sub

NickHK

OOC said:
I am using the below program to iterate rows and calculate pipe weights based
on several values. When my "A" variable exceeds "4", my answer returns "0".
Any size 4 and below on the A variable works perfectly. I have checked my
tables and ensured that they were numbers and declared them as strings in the
code. I ahve stepped into the code and it functions fine until it exceeds
the value. Anyone with any ideas? I am going CRAZY!



'This code is for calculating weight of pipe according to size, schedule,
type and length.

Sub Weight1()
'Declare Variables
Dim Length As String
Dim Answer As String
Dim A As String
'
'Declare a counter to iterate through each line of the piping spreadsheet
and calculate each line.
'Set length to zero if no length is entered on spreadsheet.
'Set Answer variable to zero between iterations.
Dim Counter1 As Integer
For Counter1 = 8 To 34
Length = Worksheets("Piping").Cells(Counter1, 11).Value
A = Worksheets("Piping").Cells(Counter1, 6).Value
If Length = "" Then Length = 0
Answer = 0
'
'The following statements sort the Appendix according to schedule and pipe
type and then
'calculates weight.
If Worksheets("Piping").Cells(Counter1, 7).Value = 10 Then
If Worksheets("Piping").Cells(Counter1, 10).Value = "A106B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 2) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "A53 Gr B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 3) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "304" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 4) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "216" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 5) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "321" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("A2:F23"), 6) * Length
ElseIf Not IsEmpty(Worksheets("Piping").Cells(Counter1, 10).Value) Then
MsgBox ("Check Material Type")
End If
ElseIf Worksheets("Piping").Cells(Counter1, 7).Value = 40 Then
If Worksheets("Piping").Cells(Counter1, 10).Value = "A106B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 2) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "A53 Gr B" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 3) * Length
ElseIf Worksheets("Piping").Cells(Counter1, 10).Value = "304" Then
Answer = Excel.WorksheetFunction.VLookup(A, Worksheets("Appendix
A").Range("H2:M23"), 4) * Length
Click to expand...
---------- CUT ---------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MACRO RUN random percentage and copy of column values 1
Help Get This Macro Running in OS X Please 9
SetSourceData for Chart 2
Backwards compatibile macro 0
slow code with many loopings 6
move data from one spreadsheet to another and repeat 1
VBA Error Message HELP!!!!! 0
application-defined or object defined error 1

Top