Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until€¦Loop Question for the Excel-Looping-Experts
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 dont 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!! -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until€¦Loop Question for the Excel-Looping-Experts
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 "ryguy7272" wrote: 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 dont 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!! -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until€¦Loop Question for the Excel-Looping-Experts
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 In article , ryguy7272 wrote: 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 dont 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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until€¦Loop Question for the Excel-Looping-Experts
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until...Loop Question for the Excel-Looping-Experts
Your second procedure only evaluates the first row because 'i' and 'j'
are always equal 1. Hth, Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until...Loop Question for the Excel-Looping-Experts
Correction. Your second procedure only evaluates the first row because
'i' and 'j' are always equal their first row values. Hth, Merjet |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until.Loop Question for the Excel-Looping-Experts
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, "ryguy7272" wrote in message ... 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!! -- RyGuy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Until.Loop Question for the Excel-Looping-Experts
Excellent!! I knew you guys would come through for me big time!! My New
Years resolution was to become highly proficient with Excel by the end of 07. Im 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 "George Nicholson" wrote: 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, "ryguy7272" wrote in message ... 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!! -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Filtering Question for Excel 2007 Experts | New Users to Excel | |||
Looping a loop? | Excel Programming | |||
Question for the real experts! | Excel Discussion (Misc queries) | |||
Question to experts VBA | Excel Programming | |||
Could one of you experts answer an easy question... | Excel Programming |