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