Do Until…Loop Question for the Excel-Looping-Experts

[ryguy7272]

Quick question for you Excel-Looping-Experts out there…

Today I was working with a relatively simple Do Until…Loop. I got stuck, so
I turned on the Macro Recorded and got a couple of lines of code from that.
My code is below…


Sub Compare_AB()
Dim i, j As Integer
Range("A1").Select
i = ActiveCell.Value
Range("B1").Select
j = ActiveCell.Value

Do Until ActiveCell = ""
If i = j Then
Selection.Offset(0, 2).Select

ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Yes"",""No"")"

End If

Selection.Offset(1, 0).Select
Selection.Offset(0, -2).Select


Loop
End Sub


Yes, I know this is not very eloquent, but it actually works exactly like I
want it to (basically, it compares values in Column A with values in Column
B; if the two match, a “Yes†is placed in Column D, but if they don’t match a
“No†is placed in column D). Now, my question is two fold:

1) How can I make the code more efficient (i.e., get rid of the
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Yes"",""No"")" and
Selection.Offset(1, 0).Select and Selection.Offset(0, -2).Select)?

2) The code below simply places a “Yes†in Column D, seemingly without
evaluating the values in Column A and Column B. Why does the code below not
evaluate all of the elements in Column A and Column B?

Sub Compare_AB()
Dim i, j As Integer
Range("A1").Select
i = ActiveCell.Value
Range("B1").Select
j = ActiveCell.Value
Do Until ActiveCell = ""
If i = j Then
Selection.Offset(0, 2).Select

ActiveCell.Value = "Yes"

If i <> j Then
ActiveCell.Value = "No"

End If
End If

Selection.Offset(1, 0).Select
Selection.Offset(0, -2).Select


Loop
End Sub


Thanks for your time and effort!!

 

[Jason]

Hi, RyGuy. Try using this code instead:

Sub Compare_AB()

Dim cell As Range, rng As Range

Set rng = Range("A1:A" & Cells.SpecialCells(xlCellTypeLastCell).Row)

For Each cell In rng

If cell = cell.Offset(1, 0) Then
cell.Offset(0, 2) = "Yes"
Else
cell.Offset(0, 2) = "No"
End If

Next

End Sub

-Jason

 

[JE McGimpsey]

One way:

Public Sub Compare_AB()
Dim rCell As Range

With ActiveSheet
For Each rCell In .Range("A1:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
With rCell
If .Value = .Offset(0, 1).Value Then
.Offset(0, 3).Value = "Yes"
Else
.Offset(0, 3).Value = "No"
End If
End With
Next rCell
End With
End Sub

 

[Jason]

Sorry I had a slight typo in the previous post. See revised code below.

Sub Compare_AB()

Dim cell As Range, rng As Range

Set rng = Range("A1:A" & Cells.SpecialCells(xlCellTypeLastCell).Row)

For Each cell In rng

If cell = cell.Offset(0, 1) Then
cell.Offset(0, 2) = "Yes"
Else
cell.Offset(0, 2) = "No"
End If

Next

End Sub

Also, if you want to leave a blank cell, rather than No, then delete the
full If function above, and replace with:

If cell = cell.Offset(0, 1) Then
cell.Offset(0, 2) = "Yes"
ElseIf cell = "" Or cell.Offset(0, 1) = "" Then
cell.Offset(0, 2) = ""
Else
cell.Offset(0, 2) = "No"
End If

 

[merjet]

Your second procedure only evaluates the first row because 'i' and 'j'
are always equal 1.

Hth,
Merjet

 

[merjet]

Correction. Your second procedure only evaluates the first row because
'i' and 'j'
are always equal their first row values.

Hth,
Merjet

 

[George Nicholson]

First, the values being compared never change. You only set the values of i
and j once, before the loop starts. Whatever values they hold initially is
going to determine whether Yes or No is placed in column D for every row.
You need to move the i & j assignments to inside the loop so that each row
will be evaluated. Corrected below.

Second you have a simple If..Then...Else condition stated as two badly
nested If...Thens (no Else):

"Test if i=j, if it does then ActiveCell = yes, AND check to see if i<>j
...."

See it? You are only testing i<>j when i= j. The i <>j test will always
return False and never assign No as a value. Corrected below.

Third, minimize (or eliminate when possible) Selects. Unnecessary
performance hit (and can make things confusing as all get out, but maybe
that's just me..). The below reduces selects to once per row (note that
ActiveCell will now always refer to column A). There are ways to
reduce/eliminate selects entirely, but this gets you started.

Dim i as Integer, j As Integer
Range("A1").Select

Do Until ActiveCell.Value= ""

i = ActiveCell.Value
j = ActiveCell.Offset(0,1).Value

If i = j Then
ActiveCell.Offset(0, 3).Value = "Yes"
Else
' Note: i<>j
ActiveCell.Offset(0, 3).Value = "No"
End If

'Move down a row
ActiveCell.Offset(1, 0).Select

Loop


HTH,

Quick question for you Excel-Looping-Experts out there.

Today I was working with a relatively simple Do Until.Loop. I got stuck,
so
I turned on the Macro Recorded and got a couple of lines of code from
that.
My code is below.


Sub Compare_AB()
Dim i, j As Integer
Range("A1").Select
i = ActiveCell.Value
Range("B1").Select
j = ActiveCell.Value

Do Until ActiveCell = ""
If i = j Then
Selection.Offset(0, 2).Select

ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Yes"",""No"")"

End If

Selection.Offset(1, 0).Select
Selection.Offset(0, -2).Select


Loop
End Sub


Yes, I know this is not very eloquent, but it actually works exactly like
I
want it to (basically, it compares values in Column A with values in
Column
B; if the two match, a "Yes" is placed in Column D, but if they don't
match a
"No" is placed in column D). Now, my question is two fold:

1) How can I make the code more efficient (i.e., get rid of the
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Yes"",""No"")" and
Selection.Offset(1, 0).Select and Selection.Offset(0, -2).Select)?

2) The code below simply places a "Yes" in Column D, seemingly without
evaluating the values in Column A and Column B. Why does the code below
not
evaluate all of the elements in Column A and Column B?

Sub Compare_AB()
Dim i, j As Integer
Range("A1").Select
i = ActiveCell.Value
Range("B1").Select
j = ActiveCell.Value
Do Until ActiveCell = ""
If i = j Then
Selection.Offset(0, 2).Select

ActiveCell.Value = "Yes"

If i <> j Then
ActiveCell.Value = "No"

End If
End If

Selection.Offset(1, 0).Select
Selection.Offset(0, -2).Select


Loop
End Sub


Thanks for your time and effort!!

 

[ryguy7272]

Excellent!! I knew you guys would come through for me big time!! My New
Year’s resolution was to become highly proficient with Excel by the end of
’07. I’m getting closer to this personal goal every single day, but still
have a ways to go. Nevertheless, whenever possible, I try to help others in
this community, just like those that frequent here have helped me.

--
RyGuy

First, the values being compared never change. You only set the values of i
and j once, before the loop starts. Whatever values they hold initially is
going to determine whether Yes or No is placed in column D for every row.
You need to move the i & j assignments to inside the loop so that each row
will be evaluated. Corrected below.

Second you have a simple If..Then...Else condition stated as two badly
nested If...Thens (no Else):

"Test if i=j, if it does then ActiveCell = yes, AND check to see if i<>j
...."

See it? You are only testing i<>j when i= j. The i <>j test will always
return False and never assign No as a value. Corrected below.

Third, minimize (or eliminate when possible) Selects. Unnecessary
performance hit (and can make things confusing as all get out, but maybe
that's just me..). The below reduces selects to once per row (note that
ActiveCell will now always refer to column A). There are ways to
reduce/eliminate selects entirely, but this gets you started.

Dim i as Integer, j As Integer
Range("A1").Select

Do Until ActiveCell.Value= ""

i = ActiveCell.Value
j = ActiveCell.Offset(0,1).Value

If i = j Then
ActiveCell.Offset(0, 3).Value = "Yes"
Else
' Note: i<>j
ActiveCell.Offset(0, 3).Value = "No"
End If

'Move down a row
ActiveCell.Offset(1, 0).Select

Loop


HTH,

Quick question for you Excel-Looping-Experts out there.

Today I was working with a relatively simple Do Until.Loop. I got stuck,
so
I turned on the Macro Recorded and got a couple of lines of code from
that.
My code is below.


Sub Compare_AB()
Dim i, j As Integer
Range("A1").Select
i = ActiveCell.Value
Range("B1").Select
j = ActiveCell.Value

Do Until ActiveCell = ""
If i = j Then
Selection.Offset(0, 2).Select

ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Yes"",""No"")"

End If

Selection.Offset(1, 0).Select
Selection.Offset(0, -2).Select


Loop
End Sub


Yes, I know this is not very eloquent, but it actually works exactly like
I
want it to (basically, it compares values in Column A with values in
Column
B; if the two match, a "Yes" is placed in Column D, but if they don't
match a
"No" is placed in column D). Now, my question is two fold:

1) How can I make the code more efficient (i.e., get rid of the
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=RC[-2],""Yes"",""No"")" and
Selection.Offset(1, 0).Select and Selection.Offset(0, -2).Select)?

2) The code below simply places a "Yes" in Column D, seemingly without
evaluating the values in Column A and Column B. Why does the code below
not
evaluate all of the elements in Column A and Column B?

Sub Compare_AB()
Dim i, j As Integer
Range("A1").Select
i = ActiveCell.Value
Range("B1").Select
j = ActiveCell.Value
Do Until ActiveCell = ""
If i = j Then
Selection.Offset(0, 2).Select

ActiveCell.Value = "Yes"

If i <> j Then
ActiveCell.Value = "No"

End If
End If

Selection.Offset(1, 0).Select
Selection.Offset(0, -2).Select


Loop
End Sub


Thanks for your time and effort!!