ExcelBanter - Loops in a function

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Loops in a function (https://www.excelbanter.com/excel-programming/351967-loops-function.html)

Loops in a function

I hope someone can figure out what i am doing wrong

here is the code, then the explanation at the end

_____________________________________

Dim WS As Range
Dim Alamps As Variant
Dim Alamps2 As Variant
Dim Cuamps As Variant
Dim A As Worksheet
Dim B As Worksheet
Dim intCounter As Integer
Dim Temp As Range
Dim TAl As Integer
Dim TCu As Integer

Function NOAMPS(WS)

Set A = Worksheets("ALL PRICES")
Set Temp = Worksheets("ALL PRICES").Range("C17")

Select Case Temp
Case Is = 60
TAl = 1
TCu = 4
Case Is = 75
TAl = 2
TCu = 5
Case Is = 90
TAl = 3
TCu = 6
End Select

Cuamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 6,
False)
Alamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 3,
False)

If Alamps = Cuamps Then
NOAMPS = Application.WorksheetFunction.vlookup(Alamps,
A.Range("F7:M36"), 8, False)
Else
intCounter = 0
Do
intCounter = intCounter + 1
Alamps2 = Application.WorksheetFunction.Index(A.Range("F7:F3 6"), _

Application.WorksheetFunction.Match(Application.Wo rksheetFunction.vlookup(WS,
A.Range("F7:M36"), 3, False), _
A.Range("G7:G36")) + 1)
NOAMPS = Application.WorksheetFunction.vlookup(Alamps2,
A.Range("F7:M36"), 8, False)
Loop Until Alamps2 = Cuamps
End If
End Function

____________________________

The function of this code is to be able to look up a wire size base on the
wire size and composition. So when it was looking up a the wire size i want
it to compare the amp rating for the wire to the other. The wire selection is
then based apon if the wire is either greater than or equal to the wire size
that it is being compared to..

Thanks again guys

Andy

Loops in a function

Hi Andy,

Can you explain what the problem is? For example does this produce a
particular error message or does it fall over at a particular line in the
code?

Best regards

John

"Andy" wrote in message
...
I hope someone can figure out what i am doing wrong

here is the code, then the explanation at the end

_____________________________________

Dim WS As Range
Dim Alamps As Variant
Dim Alamps2 As Variant
Dim Cuamps As Variant
Dim A As Worksheet
Dim B As Worksheet
Dim intCounter As Integer
Dim Temp As Range
Dim TAl As Integer
Dim TCu As Integer

Function NOAMPS(WS)

Set A = Worksheets("ALL PRICES")
Set Temp = Worksheets("ALL PRICES").Range("C17")

Select Case Temp
Case Is = 60
TAl = 1
TCu = 4
Case Is = 75
TAl = 2
TCu = 5
Case Is = 90
TAl = 3
TCu = 6
End Select

Cuamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"),
6,
False)
Alamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"),
3,
False)

If Alamps = Cuamps Then
NOAMPS = Application.WorksheetFunction.vlookup(Alamps,
A.Range("F7:M36"), 8, False)
Else
intCounter = 0
Do
intCounter = intCounter + 1
Alamps2 = Application.WorksheetFunction.Index(A.Range("F7:F3 6"), _

Application.WorksheetFunction.Match(Application.Wo rksheetFunction.vlookup(WS,
A.Range("F7:M36"), 3, False), _
A.Range("G7:G36")) + 1)
NOAMPS = Application.WorksheetFunction.vlookup(Alamps2,
A.Range("F7:M36"), 8, False)
Loop Until Alamps2 = Cuamps
End If
End Function

____________________________

The function of this code is to be able to look up a wire size base on the
wire size and composition. So when it was looking up a the wire size i
want
it to compare the amp rating for the wire to the other. The wire selection
is
then based apon if the wire is either greater than or equal to the wire
size
that it is being compared to..

Thanks again guys

Andy

Loops in a function

Andy,
What exactly is wrong?

The variable "intCounter" doesn't appear to do anything and the DO LOOP ( it
appears to me) will always give the same value for "Alamps2" for a given WS
i.e. the check Alamps2= Cuamps only works if Alamps2 is changed within the
loop or is on the first pass through.

"Andy" wrote:

I hope someone can figure out what i am doing wrong

here is the code, then the explanation at the end

_____________________________________

Dim WS As Range
Dim Alamps As Variant
Dim Alamps2 As Variant
Dim Cuamps As Variant
Dim A As Worksheet
Dim B As Worksheet
Dim intCounter As Integer
Dim Temp As Range
Dim TAl As Integer
Dim TCu As Integer

Function NOAMPS(WS)

Set A = Worksheets("ALL PRICES")
Set Temp = Worksheets("ALL PRICES").Range("C17")

Select Case Temp
Case Is = 60
TAl = 1
TCu = 4
Case Is = 75
TAl = 2
TCu = 5
Case Is = 90
TAl = 3
TCu = 6
End Select

Cuamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 6,
False)
Alamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 3,
False)

If Alamps = Cuamps Then
NOAMPS = Application.WorksheetFunction.vlookup(Alamps,
A.Range("F7:M36"), 8, False)
Else
intCounter = 0
Do
intCounter = intCounter + 1
Alamps2 = Application.WorksheetFunction.Index(A.Range("F7:F3 6"), _

Application.WorksheetFunction.Match(Application.Wo rksheetFunction.vlookup(WS,
A.Range("F7:M36"), 3, False), _
A.Range("G7:G36")) + 1)
NOAMPS = Application.WorksheetFunction.vlookup(Alamps2,
A.Range("F7:M36"), 8, False)
Loop Until Alamps2 = Cuamps
End If
End Function

____________________________

The function of this code is to be able to look up a wire size base on the
wire size and composition. So when it was looking up a the wire size i want
it to compare the amp rating for the wire to the other. The wire selection is
then based apon if the wire is either greater than or equal to the wire size
that it is being compared to..

Thanks again guys

Andy

Loops in a function

I am getting a "#VALUE!" error

and i made a mistake with the last code so here it is again. i put the
inCounter in the appropriate spot, i hope.

Dim WS As Range
Dim Alamps As Variant
Dim Alamps2 As Variant
Dim Cuamps As Variant
Dim A As Worksheet
Dim B As Worksheet
Dim intCounter As Integer
Dim Temp As Range
Dim TAl As Integer
Dim TCu As Integer

Function NOAMPS(WS)

Set A = Worksheets("ALL PRICES")

Cuamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 6,
False)
Alamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 3,
False)

If Alamps = Cuamps Then
NOAMPS = Application.WorksheetFunction.vlookup(Alamps,
A.Range("F7:M36"), 8, False)
Else
intCounter = 0
Do
intCounter = intCounter + 1
Alamps2 = Application.WorksheetFunction.Index(A.Range("F7:F3 6"), _

Application.WorksheetFunction.Match(Application.Wo rksheetFunction.vlookup(WS,
A.Range("F7:M36"), 3, False), _
A.Range("G7:G36")) + intCounter)

Loop Until Alamps2 = Cuamps
NOAMPS = Application.WorksheetFunction.vlookup(Alamps2,
A.Range("F7:M36"), 8, False)
End If
End Function
"Toppers" wrote:

Andy,
What exactly is wrong?

The variable "intCounter" doesn't appear to do anything and the DO LOOP ( it
appears to me) will always give the same value for "Alamps2" for a given WS
i.e. the check Alamps2= Cuamps only works if Alamps2 is changed within the
loop or is on the first pass through.

"Andy" wrote:

I hope someone can figure out what i am doing wrong

here is the code, then the explanation at the end

_____________________________________

Dim WS As Range
Dim Alamps As Variant
Dim Alamps2 As Variant
Dim Cuamps As Variant
Dim A As Worksheet
Dim B As Worksheet
Dim intCounter As Integer
Dim Temp As Range
Dim TAl As Integer
Dim TCu As Integer

Function NOAMPS(WS)

Set A = Worksheets("ALL PRICES")
Set Temp = Worksheets("ALL PRICES").Range("C17")

Select Case Temp
Case Is = 60
TAl = 1
TCu = 4
Case Is = 75
TAl = 2
TCu = 5
Case Is = 90
TAl = 3
TCu = 6
End Select

Cuamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 6,
False)
Alamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 3,
False)

If Alamps = Cuamps Then
NOAMPS = Application.WorksheetFunction.vlookup(Alamps,
A.Range("F7:M36"), 8, False)
Else
intCounter = 0
Do
intCounter = intCounter + 1
Alamps2 = Application.WorksheetFunction.Index(A.Range("F7:F3 6"), _

Application.WorksheetFunction.Match(Application.Wo rksheetFunction.vlookup(WS,
A.Range("F7:M36"), 3, False), _
A.Range("G7:G36")) + 1)
NOAMPS = Application.WorksheetFunction.vlookup(Alamps2,
A.Range("F7:M36"), 8, False)
Loop Until Alamps2 = Cuamps
End If
End Function

____________________________

The function of this code is to be able to look up a wire size base on the
wire size and composition. So when it was looking up a the wire size i want
it to compare the amp rating for the wire to the other. The wire selection is
then based apon if the wire is either greater than or equal to the wire size
that it is being compared to..

Thanks again guys

Andy

Loops in a function

Andy,
Is the "Value" error in the Alamps2 (index) statement? If so,
it suggests the row/coumn values returned are non-numeric.

If you want, post w/book to me ) and I'll
have a look.

HTH

"Andy" wrote:

I am getting a "#VALUE!" error

and i made a mistake with the last code so here it is again. i put the
inCounter in the appropriate spot, i hope.

Dim WS As Range
Dim Alamps As Variant
Dim Alamps2 As Variant
Dim Cuamps As Variant
Dim A As Worksheet
Dim B As Worksheet
Dim intCounter As Integer
Dim Temp As Range
Dim TAl As Integer
Dim TCu As Integer

Function NOAMPS(WS)

Set A = Worksheets("ALL PRICES")

Cuamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 6,
False)
Alamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 3,
False)

If Alamps = Cuamps Then
NOAMPS = Application.WorksheetFunction.vlookup(Alamps,
A.Range("F7:M36"), 8, False)
Else
intCounter = 0
Do
intCounter = intCounter + 1
Alamps2 = Application.WorksheetFunction.Index(A.Range("F7:F3 6"), _

Application.WorksheetFunction.Match(Application.Wo rksheetFunction.vlookup(WS,
A.Range("F7:M36"), 3, False), _
A.Range("G7:G36")) + intCounter)

Loop Until Alamps2 = Cuamps
NOAMPS = Application.WorksheetFunction.vlookup(Alamps2,
A.Range("F7:M36"), 8, False)
End If
End Function
"Toppers" wrote:

Andy,
What exactly is wrong?

The variable "intCounter" doesn't appear to do anything and the DO LOOP ( it
appears to me) will always give the same value for "Alamps2" for a given WS
i.e. the check Alamps2= Cuamps only works if Alamps2 is changed within the
loop or is on the first pass through.

"Andy" wrote:

I hope someone can figure out what i am doing wrong

here is the code, then the explanation at the end

_____________________________________

Dim WS As Range
Dim Alamps As Variant
Dim Alamps2 As Variant
Dim Cuamps As Variant
Dim A As Worksheet
Dim B As Worksheet
Dim intCounter As Integer
Dim Temp As Range
Dim TAl As Integer
Dim TCu As Integer

Function NOAMPS(WS)

Set A = Worksheets("ALL PRICES")
Set Temp = Worksheets("ALL PRICES").Range("C17")

Select Case Temp
Case Is = 60
TAl = 1
TCu = 4
Case Is = 75
TAl = 2
TCu = 5
Case Is = 90
TAl = 3
TCu = 6
End Select

Cuamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 6,
False)
Alamps = Application.WorksheetFunction.vlookup(WS, A.Range("G7:M36"), 3,
False)

If Alamps = Cuamps Then
NOAMPS = Application.WorksheetFunction.vlookup(Alamps,
A.Range("F7:M36"), 8, False)
Else
intCounter = 0
Do
intCounter = intCounter + 1
Alamps2 = Application.WorksheetFunction.Index(A.Range("F7:F3 6"), _

Application.WorksheetFunction.Match(Application.Wo rksheetFunction.vlookup(WS,
A.Range("F7:M36"), 3, False), _
A.Range("G7:G36")) + 1)
NOAMPS = Application.WorksheetFunction.vlookup(Alamps2,
A.Range("F7:M36"), 8, False)
Loop Until Alamps2 = Cuamps
End If
End Function

____________________________

The function of this code is to be able to look up a wire size base on the
wire size and composition. So when it was looking up a the wire size i want
it to compare the amp rating for the wire to the other. The wire selection is
then based apon if the wire is either greater than or equal to the wire size
that it is being compared to..

Thanks again guys

Andy