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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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)!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top