Do While Do While Do While Loop Loop Loop

[KaibleBasha]

Hi,

Are you able to see where I would be getting stuck in the below?
I am trying to ascertain which values in a given cell are matching the values in cells B,C,D,E,F & G2
I have 5 groups of data (hence z<6), 24 columns (hence Y<=t(where t is increased to 24)) and 35 rows (hence x<=u(where u is increased to 35)). I haven't set any actions for z as yet as it keeps crashing in the first set.
Dim b As Integer, c As Integer, d As Integer, e As Integer, f As Integer, g As Integer, w As Integer
b = Range("B2").Value
c = Range("C2").Value
d = Range("D2").Value
e = Range("E2").Value
f = Range("F2").Value
g = Range("G2").Value
w = Range("J2").Value
Dim x As Integer, y As Integer, z As Integer, t As Integer, u As Integer
z = 1
y = 2 'column 2 ("B")
x = 5 'row 5
t = 8 'up to column 8 ("H")
u = 12 'down to row 12
On Error Resume Next
Do While z < 6
Do While y <= t
If y = 8 Then y = y + 2 And t = t + 8
If y = 16 Then y = y + 2 And t = t + 8
Do While x <= u 'while 5<=12
If x = 12 Then x = x + 9 And u = u + 23 'if x=12 then x=21 and u=35
If Cells(x, y) = b Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = c Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = d Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = e Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = f Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = g Then Cells(x, y).Style = "Accent1"
x = x + 1
Loop
If x = 35 Then x = x - 30 And u = u - 23 'if x=35 then x=5 and u=12
y = y + 1 'moves one column to the right e.g. 2 is column "B", 3 is "C" etc etc to a max of column "X".
Loop
z = z + 1
Loop

My pc stops when I try to run this which is why I think it's stuck in the loops as it should only go round 5 times right?

Thanks
Kai

 

[RickXL]

Hi,

I don't think these lines are right. You have several in the same format.

If x = 12 Then x = x + 9 And u = u + 23

I think what you mean is:

If x = 12 Then 
    x = x + 9 
    u = u + 23
end if

What it is doing is:
If x = 12 Then x = (x + 9 And u = u + 23)
which the first time round works out as:
If x = 12 Then x = (21 And (12 = 37))
then:
If x = 12 Then x = (21 And (False))
then
If x = 12 Then x = False
So x ends up as zero (the same as False)!

 

[KaibleBasha]

Hi

Thanks for the response. The macro now runs through however it doesn match of the values, would this be due to how they are set up? e.g. b=Range9"B2").value

This is how it looks now;
Dim b As Integer, c As Integer, d As Integer, e As Integer, f As Integer, g As Integer, w As Integer
b = Range("B2").Value
c = Range("C2").Value
d = Range("D2").Value
e = Range("E2").Value
f = Range("F2").Value
g = Range("G2").Value
w = Range("J2").Value
Dim x As Integer, y As Integer, z As Integer, t As Integer, u As Integer
z = 1
y = 2 'column 2 ("B")
x = 5 'row 5
t = 8 'up to column 8 ("H")
u = 12 'down to row 12
On Error Resume Next
Do While z < 6
Do While y <= t 'while 2<=8
If y = 8 Then
y = y + 2
t = t + 8
End If
If y = 16 Then
y = y + 2
t = t + 8
End If
Do While x <= u 'while 5<=12
If x = 12 Then
x = x + 9
u = u + 23 'if x=12 then x=21 and u=35
End If
If Cells(x, y) = b Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = c Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = d Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = e Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = f Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = g Then Cells(x, y).Style = "Accent1"
x = x + 1
Loop
If x = 35 Then
x = x - 30
u = u - 23 'if x=35 then x=5 and u=12
End If
y = y + 1 'moves one column to the right e.g. 2 is column "B", 3 is "C" etc etc to a max of column "X".
Loop
z = z + 1
Loop


Many thanks
Kai

 

[RickXL]

Hi,

Perhaps you should try some debugging. Use the F5 key to step through the code and hover the mouse over the variables to see what they are.

I just tried by putting 99 into both B2 and B5 and the first time round the loop B5 was shaded blue.

 

[KaibleBasha]

Hi Rick,

Thanks again for the help on this and VBA counting issues I was having.

I did some debugging as suggested and found that the values of x,y,z,t & u were returning as 0, so only cells(0,0) would be checked :P

I rejigged it slightly and now it runs through each cell I need it to and also highlights the matches, this is what I now have running if you're interested / have any suggestions for this;

Sub LoopLoopLoop()
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer
Dim g As Integer
Dim w As Integer
b = Range("B2").Value
c = Range("C2").Value
d = Range("D2").Value
e = Range("E2").Value
f = Range("F2").Value
g = Range("G2").Value
w = Range("J2").Value
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim t As Integer
Dim u As Integer
Do While z <= 6
If z = 1 Then
y = 2
x = 5
t = 8
u = 12
End If
If z = 2 Then
y = 2
x = 21
t = 16
u = 35
End If
If z = 3 Then
y = 10
x = 5
t = 16
u = 12
End If
If z = 4 Then
y = 10
x = 21
t = 16
u = 35
End If
If z = 5 Then
y = 18
x = 5
t = 24
u = 12
End If
If z = 6 Then
y = 18
x = 21
t = 24
u = 35
End If
Do While x < u
Do While y < t
If Cells(x, y) = b Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = c Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = d Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = e Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = f Then Cells(x, y).Style = "Accent1"
If Cells(x, y) = g Then Cells(x, y).Style = "Accent1"
y = y + 1
Loop
x = x + 1
If z < 3 Then y = 2
If z < 5 And z > 2 Then y = 10
If z <= 6 And z > 4 Then y = 18
Loop
z = z + 1
Loop
End Sub



Thanks
Kai

 

[RickXL]

Hi, I am pleased that you got it working.

I can see how Cells(0, 0) might cause a problem


By the way, for future reference, it is preferred if you put CODE tags round your code. Just select your code and hit the # button above. It makes it easier to read and keeps any indentations.

Thanks,

 

[nolos]

Hi, I am new in the blog and I have some trouble with a loop.

I did the next code to generate a random value (Range C4) and keep adding until 100 is reached.

The code works fine but I want to make it in a loop.

Also, There are 3 conditions:
1) If range C6 have a 1 the variable "valor" wil be + 5
2) If range D6 have a 1 the variable "valor" will be - 1
3) If range C6 and range D6 both sum 2: first C6 will get 5 and the total sum will be -1

Is there a way to make it using loop as well? or maybe case if?

Thank you so much with the help!

Cheers
Hector

Sub Crashers()

valor = Range("d4").Value

'=========================== VALUES =============================
If valor < 100 Then

Randomize
Range("c4").Value = Int(20 * Rnd) + 1

If Range("c6") = 1 Then
valor = valor + (Range("c4").Value + 5)
Range("c6").Value = 0
Range("d4").Value = valor

Else

If Range("d6") = 1 Then
valor = (valor - 1) + Range("c4").Value
Range("d6").Value = 0
Range("d4").Value = valor
Else


If (Range("c6") + Range("d6")) = 0 Then
valor = valor + Range("c4").Value
Range("d4").Value = valor
Else

End If
End If
End If

'=========================== VALUES =============================
Else
Range("C4:D4").Select
Selection.ClearContents
Range("C4").Select

Range("d4").Value = 0
Range("c4").Value = 0

End If
End Sub