Do While Loop Using counter and equal variable conditions

[Prevost]

Hi There,

I am trying to run a do while loop with two conditions but I can't get it to work properly. The user selects a wire gauge, and then the macro compares the selected cell value to a list of existing gauge values to determine whether or not any of the match up, hence the AWGCell(1 to 55) that is used to list all the wire gauges. I want the do while loop to continue until the gauge value equals one of the listed values OR until the counter exceeds 55. However, with this I cannot get it to work for some reason. Gauge and getgauge become equal to each other but then the loop just continues.

Thanks!

Dim x as integer
Dim getgauge, gauge, AWGCell(1 to 55) as double
[I list all the values of AWGCell(1 to 55)]
x=1
getgauge = Application.InputBox(Prompt:="Select Gauge", Type:=8)
gauge = AWGCell(x)
Do While gauge <> getgauge Or x < 56
gauge = AWGCell(x)
x = x + 1
Loop
End

 

[craig.penny]

Hi Prevost,

Instead of

Do While gauge <> getgauge Or x < 56

Try

Do While gauge <> getgauge And x < 56

 

[Prevost]

Hi craig.penny, thanks for responding. I haven't tried that adjustment yet, but wouldn't using the And condition only be met if gauge=getgauge when x=56? Because as it goes through the loop, it is looking for both of those conditions to be true. If the wire gauge entered into the cell is equal to 12, the getgauge=12 and AWG(1) also =12 (because that is the largest wire I am looking at) so then the gauge=getgauge condition would be true but since x=1 (less than 56) it would continue to loop. Again, I haven't made that adjustment so I am not sure if that code works but that was my logic for using OR instead of AND.
Thanks!

 

[Rick Rothstein]

I am not sure I understand the logic of what you are doing. It looks like you are asking the user to guess at a gauge size and then you loop through your 55 actual gauges to see if the user guessed right... if so, you set the gauge variable accordingly. I am wondering why you don't just present the user with the list of 55 known gauges and let him/her pick one from the list. Anyway, staying with the code you posted, I would probably throw the Do..Loop away and use a normal For..Next loop instead (I think the logic is easier to follow). I am thinking something like this...

Dim x As Long
Dim getgauge As Double, gauge As Double, AWGCell(1 To 55) As Double
getgauge = Application.InputBox(Prompt:="Select Gauge", Type:=8)
For x = 1 To 55
  If AWGCell(x) = getgauge Then
    gauge = getgauge
    Exit For
  End If
Next

Note 1: Use Long data types instead of Integer (the Integer data type saves nothing on modern computers)

Note 2: I presume gauge and getgauge were meant to be Doubles, but the way you declared them, they were Variants... in VB, you must declare each variable's data type individually.

 

[Prevost]

Hi Rick,
Thanks for the change. I had previously changed it to a Do Loop and had the conditions as the bottom which seemed to work, but this is more straightforward because you don't have to think about whether or not the AWGCell(x) will be out of range by adding 1 to the counter (because the condition is at the bottom of the loop, it's not a lot to think about by any means but the For loop is more intuitive). As for the purpose of what I am doing, the wire strands and wire gauges are already existing in the spreadsheet, so it's faster to select the cells for the strands and gauges then to select them from a list. I have also made some vba to perform the same calculations on an entire range (because some spreadsheets contain columns of strands and ranges that I need to perform calculations on). I started out with doing them one at a time to understand how to do it, then I am moving onto selecting entire ranges (I am relatively new). I have posted the code below. The purpose is to calculate the total area of all the strands of wire, based on what gauge they are.

Sub CalculateCM()
Dim getstrands As Long, strands As Long, x As Long
Dim getgauge As Double, gauge As Double
Dim refdiameter As Double, MCM As Double
Dim AWG(1 To 55) As Double
Dim AWGCell(1 To 55) As Double

refdiameter = 0.001

getstrands = Application.InputBox(Prompt:="Select Strands", Type:=8)

If IsNumeric(getstrands) = True Then
strands = getstrands
getgauge = Application.InputBox(Prompt:="Select Gauge", Type:=8)
If IsNumeric(getgauge) = True Then
For x = 1 To 55
If AWGCell(x) = getgauge Then
gauge = getgauge
MCM = strands * (AWG(x) ^ 2 / refdiameter ^ 2)
ActiveCell.Value = MCM
Exit For
End If
Next
If gauge <> getgauge Then
MsgBox ("Not A Valid Gauge Size")
Else
End If
Else
MsgBox ("Wire Gauge Is Not A Number")
End If
Else
MsgBox ("Not a Valid Strand Number")
End If
End Sub

 

[Prevost]

How can i copy and paste Code as you have done?

 

[craig.penny]

Click on the # to add code tags.

 

[Prevost]

Thanks.