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 ---------------------