Macro Edit...Please Help!

[Alex O]

I'm stuck trying to figure out one minor error with the macro below. For some reason each new string begins with the last entry from the previous row (see example column AA). Can anyone identify what the source of the error might be?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Thanks,<o></o>

Sub ConcatDataV1()
Dim LR As Long, LR2 As Long, a As Long, aa As Long, SR As Long, ER As Long, H As String
Application.ScreenUpdating = False
LR = Cells(Rows.Count, "B").End(xlUp).Row
Range("Z3:AC" & LR).ClearContents
Range("B3:B" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z3"), Unique:=True
Range("Z3").ClearContents
LR2 = Cells(Rows.Count, "Z").End(xlUp).Row
Range("AB4").Formula = "=MATCH(Z4,B:B,0)"
Range("AB4").AutoFill Destination:=Range("AB4:AB" & LR2)
Range("AC4").Formula = "=AB6-1"
Range("AC4").AutoFill Destination:=Range("AC4:AC" & LR2 - 1)
Range("AC" & LR2) = LR
For a = 5 To LR2 Step 1
SR = Range("AB" & a).Value
ER = Range("AC" & a).Value
H = ""
For aa = SR To ER Step 1
H = H & Cells(aa, "Y") & ", "
Next aa
If Right(H, 2) = ", " Then H = Left(H, Len(H) - 2)
Range("AA" & a) = H
Next a
Range("AB4:AC" & LR2).ClearContents
Columns("Z:AA").AutoFit
Range("Z4").Select
Application.ScreenUpdating = True
End Sub

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"><COL style="WIDTH: 249px"><COL style="WIDTH: 114px"><COL style="WIDTH: 84px"><COL style="WIDTH: 67px"><COL style="WIDTH: 85px"><COL style="WIDTH: 104px"><COL style="WIDTH: 68px"><COL style="WIDTH: 66px"><COL style="WIDTH: 83px"><COL style="WIDTH: 68px"><COL style="WIDTH: 144px"><COL style="WIDTH: 85px"><COL style="WIDTH: 161px"><COL style="WIDTH: 370px"><COL style="WIDTH: 249px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">STAN/10</TD><TD style="TEXT-ALIGN: left">STANISLAUS</TD><TD style="TEXT-ALIGN: left">962217</TD><TD style="TEXT-ALIGN: left">11/13/2009</TD><TD style="TEXT-ALIGN: left">2613.6</TD><TD style="TEXT-ALIGN: left">916.05</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">916.05</TD><TD style="TEXT-ALIGN: left">7/20/2011</TD><TD style="TEXT-ALIGN: left">916.05</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">JOHN W VILLINES</TD><TD style="TEXT-ALIGN: left">262.65</TD><TD style="TEXT-ALIGN: left">STAN/10-916.05</TD><TD style="TEXT-ALIGN: left">JV LAW</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">STAN/11</TD><TD style="TEXT-ALIGN: left">STANISLAUS</TD><TD style="TEXT-ALIGN: left">1020857</TD><TD style="TEXT-ALIGN: left">9/29/2010</TD><TD style="TEXT-ALIGN: left">3162.48</TD><TD style="TEXT-ALIGN: left">3393.8</TD><TD style="TEXT-ALIGN: left">3393.8</TD><TD style="TEXT-ALIGN: left">7/20/2011</TD><TD style="TEXT-ALIGN: left">3393.8</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">JOHN W VILLINES</TD><TD style="TEXT-ALIGN: left">231.32</TD><TD style="TEXT-ALIGN: left">STAN/11-3393.8</TD><TD style="TEXT-ALIGN: left">JAIME E VILLAGRANA CABINET MAKERS</TD><TD>YUBA/09-1082.57, YUBA/10-944.05</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">YUBA/09</TD><TD style="TEXT-ALIGN: left">YUBA & SUTTER COUNTIES</TD><TD style="TEXT-ALIGN: left">897015</TD><TD style="TEXT-ALIGN: left">11/21/2008</TD><TD style="TEXT-ALIGN: left">1584</TD><TD style="TEXT-ALIGN: left">1082.57</TD><TD style="TEXT-ALIGN: left">1082.57</TD><TD style="TEXT-ALIGN: left">7/21/2011</TD><TD style="TEXT-ALIGN: left">1082.57</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">JAIME VILLAGRANA</TD><TD style="TEXT-ALIGN: left">242.01</TD><TD style="TEXT-ALIGN: left">YUBA/09-1082.57</TD><TD style="TEXT-ALIGN: left">BRANDON BROWN</TD><TD>YUBA/10-944.05, SANL/10-1355.81</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">YUBA/10</TD><TD style="TEXT-ALIGN: left">YUBA & SUTTER COUNTIES</TD><TD style="TEXT-ALIGN: left">969534</TD><TD style="TEXT-ALIGN: left">1/8/2010</TD><TD style="TEXT-ALIGN: left">1729.2</TD><TD style="TEXT-ALIGN: left">944.05</TD><TD style="TEXT-ALIGN: left">944.05</TD><TD style="TEXT-ALIGN: left">7/21/2011</TD><TD style="TEXT-ALIGN: left">944.05</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">BRANDON BROWN</TD><TD style="TEXT-ALIGN: left">157.73</TD><TD style="TEXT-ALIGN: left">YUBA/10-944.05</TD><TD style="TEXT-ALIGN: left">TAHLIA'S CUCINA</TD><TD>SANL/10-1355.81, LALH/10-1673.41</TD></TR></TBODY></TABLE>

 

[Norie]

Alex

Where exactly is the problem?

 

[Ruddles]

I can't see the problem, although posting the code without any indenting made it slightly more difficult to follow. Also I couldn't get it to run with the worksheet sample you posted - I think it's expecting something in column B. However you appear to be resetting H at the start of every loop correctly.

I suggest you place a breakpoint at the For a statement and step through the code, watching the value of H as it changes. You should be able to work out where it is assigned an incorret value.

Failing that, if you can supply a more complete worksheet extract including column B, I'll have a go. Say, from A1 to AA7?

 

[Alex O]

First - Here's the entire data set.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The macro should be returning (in a single string) the value(s) in Y for each occurrence of the name in Z found in B. Does this make sense? <o></o>
<o> </o>
For example – The result in AA4 should be STAN/10-916.05, STAN/11-3393.8<o></o>
The result in AA5 should be YUBA/09-1082.57. <o></o>

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 206px"><COL style="WIDTH: 370px"><COL style="WIDTH: 49px"><COL style="WIDTH: 195px"><COL style="WIDTH: 274px"><COL style="WIDTH: 105px"><COL style="WIDTH: 39px"><COL style="WIDTH: 75px"><COL style="WIDTH: 77px"><COL style="WIDTH: 156px"><COL style="WIDTH: 123px"><COL style="WIDTH: 72px"><COL style="WIDTH: 249px"><COL style="WIDTH: 114px"><COL style="WIDTH: 84px"><COL style="WIDTH: 67px"><COL style="WIDTH: 85px"><COL style="WIDTH: 104px"><COL style="WIDTH: 68px"><COL style="WIDTH: 66px"><COL style="WIDTH: 83px"><COL style="WIDTH: 68px"><COL style="WIDTH: 144px"><COL style="WIDTH: 85px"><COL style="WIDTH: 161px"><COL style="WIDTH: 370px"><COL style="WIDTH: 249px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">361374</TD><TD style="TEXT-ALIGN: left">JV LAW</TD><TD style="TEXT-ALIGN: left">361374</TD><TD style="TEXT-ALIGN: left">726 14TH</TD><TD></TD><TD style="TEXT-ALIGN: left">Modesto</TD><TD style="TEXT-ALIGN: left">CA</TD><TD style="TEXT-ALIGN: left">95354-2507</TD><TD style="TEXT-ALIGN: left">2095249903</TD><TD style="TEXT-ALIGN: left">4309.85</TD><TD style="TEXT-ALIGN: left">11/2/2010</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">STAN/10</TD><TD style="TEXT-ALIGN: left">STANISLAUS</TD><TD style="TEXT-ALIGN: left">962217</TD><TD style="TEXT-ALIGN: left">11/13/2009</TD><TD style="TEXT-ALIGN: left">2613.6</TD><TD style="TEXT-ALIGN: left">916.05</TD><TD style="TEXT-ALIGN: left; BACKGROUND-COLOR: #ff99cc">916.05</TD><TD style="TEXT-ALIGN: left">7/20/2011</TD><TD style="TEXT-ALIGN: left">916.05</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">JOHN W VILLINES</TD><TD style="TEXT-ALIGN: left">262.65</TD><TD style="TEXT-ALIGN: left">STAN/10-916.05</TD><TD style="TEXT-ALIGN: left">JV LAW</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">361374</TD><TD style="TEXT-ALIGN: left">JV LAW</TD><TD style="TEXT-ALIGN: left">361374</TD><TD style="TEXT-ALIGN: left">726 14TH</TD><TD></TD><TD style="TEXT-ALIGN: left">MODESTO</TD><TD style="TEXT-ALIGN: left">CA</TD><TD style="TEXT-ALIGN: left">95354-2507</TD><TD style="TEXT-ALIGN: left">2095249903</TD><TD style="TEXT-ALIGN: left">4309.85</TD><TD style="TEXT-ALIGN: left">11/2/2010</TD><TD style="TEXT-ALIGN: left">STAN/11</TD><TD style="TEXT-ALIGN: left">STANISLAUS</TD><TD style="TEXT-ALIGN: left">1020857</TD><TD style="TEXT-ALIGN: left">9/29/2010</TD><TD style="TEXT-ALIGN: left">3162.48</TD><TD style="TEXT-ALIGN: left">3393.8</TD><TD style="TEXT-ALIGN: left">3393.8</TD><TD style="TEXT-ALIGN: left">7/20/2011</TD><TD style="TEXT-ALIGN: left">3393.8</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">JOHN W VILLINES</TD><TD style="TEXT-ALIGN: left">231.32</TD><TD style="TEXT-ALIGN: left">STAN/11-3393.8</TD><TD style="TEXT-ALIGN: left">JAIME E VILLAGRANA CABINET MAKERS</TD><TD>YUBA/09-1082.57, YUBA/10-944.05</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">455851</TD><TD style="TEXT-ALIGN: left">JAIME E VILLAGRANA CABINET MAKERS</TD><TD style="TEXT-ALIGN: left">455851</TD><TD style="TEXT-ALIGN: left">5272 FRANKLIN RD</TD><TD></TD><TD style="TEXT-ALIGN: left">YUBACITY</TD><TD style="TEXT-ALIGN: left">CA</TD><TD style="TEXT-ALIGN: left">95993-8620</TD><TD style="TEXT-ALIGN: left">5303156068</TD><TD style="TEXT-ALIGN: left">1082.57</TD><TD style="TEXT-ALIGN: left">2/28/2011</TD><TD style="TEXT-ALIGN: left">YUBA/09</TD><TD style="TEXT-ALIGN: left">YUBA & SUTTER COUNTIES</TD><TD style="TEXT-ALIGN: left">897015</TD><TD style="TEXT-ALIGN: left">11/21/2008</TD><TD style="TEXT-ALIGN: left">1584</TD><TD style="TEXT-ALIGN: left">1082.57</TD><TD style="TEXT-ALIGN: left">1082.57</TD><TD style="TEXT-ALIGN: left">7/21/2011</TD><TD style="TEXT-ALIGN: left">1082.57</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">JAIME VILLAGRANA</TD><TD style="TEXT-ALIGN: left">242.01</TD><TD style="TEXT-ALIGN: left">YUBA/09-1082.57</TD><TD style="TEXT-ALIGN: left">BRANDON BROWN</TD><TD>YUBA/10-944.05, SANL/10-1355.81</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">466266</TD><TD style="TEXT-ALIGN: left">BRANDON BROWN</TD><TD style="TEXT-ALIGN: left">466266</TD><TD style="TEXT-ALIGN: left">1315 DUSTIN DR APT 15</TD><TD></TD><TD style="TEXT-ALIGN: left">YUBACITY</TD><TD style="TEXT-ALIGN: left">CA</TD><TD style="TEXT-ALIGN: left">95993-2737</TD><TD style="TEXT-ALIGN: left">5302109032</TD><TD style="TEXT-ALIGN: left">944.05</TD><TD style="TEXT-ALIGN: left">2/21/2011</TD><TD style="TEXT-ALIGN: left">YUBA/10</TD><TD style="TEXT-ALIGN: left">YUBA & SUTTER COUNTIES</TD><TD style="TEXT-ALIGN: left">969534</TD><TD style="TEXT-ALIGN: left">1/8/2010</TD><TD style="TEXT-ALIGN: left">1729.2</TD><TD style="TEXT-ALIGN: left">944.05</TD><TD style="TEXT-ALIGN: left">944.05</TD><TD style="TEXT-ALIGN: left">7/21/2011</TD><TD style="TEXT-ALIGN: left">944.05</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">BRANDON BROWN</TD><TD style="TEXT-ALIGN: left">157.73</TD><TD style="TEXT-ALIGN: left">YUBA/10-944.05</TD><TD style="TEXT-ALIGN: left">TAHLIA'S CUCINA</TD><TD>SANL/10-1355.81, LALH/10-1673.41</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">653366</TD><TD style="TEXT-ALIGN: left">TAHLIA'S CUCINA</TD><TD style="TEXT-ALIGN: left">653366</TD><TD style="TEXT-ALIGN: left">133 THOMPSON AV</TD><TD></TD><TD style="TEXT-ALIGN: left">NIPOMO</TD><TD style="TEXT-ALIGN: left">CA</TD><TD style="TEXT-ALIGN: left">93444</TD><TD style="TEXT-ALIGN: left">8059310273</TD><TD style="TEXT-ALIGN: left">1355.81</TD><TD style="TEXT-ALIGN: left">3/16/2011</TD><TD style="TEXT-ALIGN: left">SANL/10</TD><TD style="TEXT-ALIGN: left">SAN LUIS OBISPO COUNTY</TD><TD style="TEXT-ALIGN: left">962884</TD><TD style="TEXT-ALIGN: left">11/13/2009</TD><TD style="TEXT-ALIGN: left">1689.6</TD><TD style="TEXT-ALIGN: left">1355.81</TD><TD style="TEXT-ALIGN: left">1355.81</TD><TD style="TEXT-ALIGN: left">7/21/2011</TD><TD style="TEXT-ALIGN: left">1355.81</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">MIGHEL ZAMBRANO</TD><TD style="TEXT-ALIGN: left">229.41</TD><TD style="TEXT-ALIGN: left">SANL/10-1355.81</TD><TD style="TEXT-ALIGN: left">MEDINA HOME REPAIR SERVICE</TD><TD>LALH/10-1673.41, OAKL/10-219.79, WCON/10-206.49, CONT/10-229.82, SANF/10-249.04, FREM/10-361.48, SANF/11-902.9, OAKL/11-952.83, WCON/11-793.62, CONT/11-709.15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">662662</TD><TD style="TEXT-ALIGN: left">MEDINA HOME REPAIR SERVICE</TD><TD style="TEXT-ALIGN: left">662662</TD><TD style="TEXT-ALIGN: left">2569 E TRIMBLE</TD><TD></TD><TD style="TEXT-ALIGN: left">SANJOSE</TD><TD style="TEXT-ALIGN: left">CA</TD><TD style="TEXT-ALIGN: left">95132-1052</TD><TD style="TEXT-ALIGN: left">4087127272</TD><TD style="TEXT-ALIGN: left">1673.41</TD><TD style="TEXT-ALIGN: left">11/2/2010</TD><TD style="TEXT-ALIGN: left">LALH/10</TD><TD style="TEXT-ALIGN: left">LOS ALTOS - LOS ALTOS HILLS</TD><TD style="TEXT-ALIGN: left">961551</TD><TD style="TEXT-ALIGN: left">11/9/2009</TD><TD style="TEXT-ALIGN: left">5649.6</TD><TD style="TEXT-ALIGN: left">1673.41</TD><TD style="TEXT-ALIGN: left">1673.41</TD><TD style="TEXT-ALIGN: left">7/21/2011</TD><TD style="TEXT-ALIGN: left">1673.41</TD><TD style="TEXT-ALIGN: left">25</TD><TD style="TEXT-ALIGN: left">WO-DEL</TD><TD style="TEXT-ALIGN: left">JAIME MEDINA</TD><TD style="TEXT-ALIGN: left">261.01</TD><TD style="TEXT-ALIGN: left">LALH/10-1673.41</TD><TD style="TEXT-ALIGN: left">ROYAL INVESTIGATION & PATROL INC</TD><TD>OAKL/10-219.79, WCON/10-206.49, CONT/10-229.82, SANF/10-249.04, FREM/10-361.48, SANF/11-902.9, OAKL/11-952.83, WCON/11-793.62, CONT/11-709.15, SONO/10-2174.58</TD></TR></TBODY></TABLE>

 

[Ruddles]

Nope, when I run the code it clears column Z, then does LR2 = Cells(Rows.Count, "Z").End(xlUp).Row which returns a value of 1, then loops round from 5 To LR2 Step 1, which goes nowhere.

Try setting a breakpoint and stepping through the code as per my earlier suggestion. I'm about to go to bed now but I'll check back on the thread tomorrow.

 

[Alex O]

I'm not sure I know what you mean by "setting a breakpoint and stepping through the code." I'm fairly new to VBA...can you expound?

Thanks

 

[Ruddles]

If you're struggling to work out what's going on in your program, you can place 'breakpoints' in your code which force VBA to pause and allow you to see what piece of code is being executed and what your variables contain, either by mousing over them or by printing their values to the Immediate window (Ctrl-G).

Switch to your VBA code window and find a statement somewhere at the start of your program - the Sub line will do. Click in the grey bar to the left of the statement and a maroon dot will appear: this indicates that the breakpoint is set. (You can't set breakpoints against non-executable statements like DIMs, comments or labels.)

Now run the program. Execution will pause at the breakpoint, a little yellow arrow will appear over the breakpoint symbol and the statement itself will be highlighted in yellow. Only statements which VBA knows it's definitely going to execute will be highlighted so the second part of a simple IF statement might not be highlighted - yet.

Press F8 (Step). The yellow highlight will move to the next executable statement. Press F8 again. Get the idea?

Now mouse over a variable name: a pop-up should appear with the variable name and its current value. Take care when you mouse over a statement containing a function: for example, if the line contains Len(strName) and you mouse over the word strName, you will see a string containing the value of strName, whereas if you mouse over the word Len, you will see a number containing the length of strName.

Whilst execution is paused, open the Immediate window (Ctrl-G). Identify the name of a variable whose value you wish to see, then in the Immediate window type the name of the variable preceded by a question mark: the value of the variable will be printed in the window. If the variable is an array, you can display its contents by typing the following in a single line:-

For i=LBound(arrayname) To UBound(arrayname) : Debug.Print i, arrayname(i) : Next i

You can add and remove breakpoints whilst the code is paused. Add another breakpoint a few lines down and press F5 (Run). The yellow highlight will skip to the new breakpoint.

You can rewrite code whilst the program is paused. If the yellow arrow is pointing at the statement you want to rewrite - i.e. it hasn't been executed yet - merely edit the statement and press F8 to execute it. If the yellow arrow has already gone past the statement you want to rewrite - i.e. it's already been executed - just edit the statement and drag the yellow arrow back up the screen to point to it, then press F8 to execute it.

You can even change the values of variable whilst execution is paused. Imagine a scenario where you've been stepping through your code for what seems like ages and you get to the point where the code has fallen over. If only intRecordPointer was equal to zero, you say, I could carry on and finish stepping through the rest of the program. Just open the Immediate window and type in intRecordPointer=0, then return to your code and resume your debugging session.

You can breakpoint as many lines of code as you want - very useful if you're not sure exactly where your program execution is going.

It's a massively useful tool once you get to grips with it.

 

[Alex O]

Ruddles,
I greatly appreciate you taking the time to provide this valuable (and detailed) explanation. This will no doubt help in my understanding some of the various aspects of creating and editing macros.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks!