| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| loops???? | Excel Worksheet Functions | |||
| if function in excel should 12 loops | Excel Discussion (Misc queries) | |||
| loops ... | Excel Programming | |||
| Using For - Next Loops in VB | New Users to Excel | |||
| Using For - Next Loops in VB | New Users to Excel | |||
Linear Mode
