ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Vlookup error (https://www.excelbanter.com/excel-programming/391577-vlookup-error.html)

OOC
Vlookup error
 
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

Tom Ogilvy
Vlookup error
 
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.

--
Regards,
Tom Ogilvy



"OOC" wrote:

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

OOC
Vlookup error
 
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!

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy



"OOC" wrote:

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

NickHK
Vlookup error
 
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" wrote in message
...
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
---------- CUT ---------------------


All times are GMT +1. The time now is 03:36 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com