Do While Do While Do While Loop Loop Loop

KaibleBasha

New Member
Joined
Aug 8, 2014
Messages
36
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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
Hi,

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

Code:
If x = 12 Then x = x + 9 And u = u + 23
I think what you mean is:
Code:
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

New Member
Joined
Aug 8, 2014
Messages
36
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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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

New Member
Joined
Aug 8, 2014
Messages
36
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

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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

New Member
Joined
Jul 27, 2015
Messages
3
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
 

Forum statistics

Threads
1,082,587
Messages
5,366,485
Members
400,894
Latest member
frog9000

Some videos you may like

This Week's Hot Topics

Top