Excel VBA Loop down list and copy value to a cell range

I am trying to write a simple VBa code in Excel to loop down a list of formula values and copy the results as text to an adjoining cell.

Here is what I have:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+l

    Sheets("Filter").Select
    Range("I2").Select
    Do While Len(ActiveCell) <> 0
        ActiveCell.Copy
        
        Range("M2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

       ActiveCell.Offset(1, 0).Select
    Loop
    
'
    
        
End Sub

Open in new window


The loop works because I have run the code without the Range("M2").PasteSpecial command.  However, when I insert this clause, it stops at the first value in the list.

Thanks

Glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
I recommend using the "Step Into" method ([F8] keyboard shortcut in VBA) to help you step through and debug what's happening.

If you open VBA and set its size so that you can see your sheet behind it, press the [F8] key while you're looking at this macro.  You'll see each line highlighted (usually bright yellow) one at a time as your press the [F8] key.  This will help you clearly see what's happening after the PasteSpecial command in line 12 of your code.

I don't want to give it away, because if you do this you'll clearly be able to see what's happening, but a clue is that your ActiveCell is no longer where you expected it to be.  

The fix is very easy; if you can't figure it out, I'll be glad to help.

Here are two very good articles here in EE that will help with using the debugging tools in VBA:
Debugging Part 1: Harnessing the Power of the Immediate Window
Debugging - Part 2

Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Or my article on debugging which applies to both VBA and VB6.
0
Glenn RayExcel VBA DeveloperCommented:
^^^Yep.  Sorry, I forgot about your very good article, Martin. :-)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Rob HensonFinance AnalystCommented:
Any particular reason for using VBA?

In M2 and down put formula:

=if(isnumber(I2),TEXT(I2,"format"),I2)

Amend the format section to how you want the number to appear. If you want the numbers to stay as numbers, then just =I2 will suffice.

Then select column M and Copy paste values.

Alternatively, just copy column I and paste as values into column M. If there are items already in M with no equivalent from I that you don't want overwritten, use the Skip Blanks option in the Paste Special.

Thanks
Rob H
0
GPSPOWAuthor Commented:
Thanks

I was able to figure out where the error was.

Glen
0
Glenn RayExcel VBA DeveloperCommented:
Yea!  I'm glad you found it on your own.  Just for curiosity sake, how did you correct your code to work as you wanted?

-Glenn
0
GPSPOWAuthor Commented:
I transferred the focus back to the list before advancing to the next row.  Plus I kept track of the row number where I needed to place the cursor.

Glen
0
Glenn RayExcel VBA DeveloperCommented:
Cool.  Lots of ways to achieve this, and that was one of the first I thought of as well.  As you progress with VBA, you'll find other methods that are more efficient.

For example, since you know you only want to copy the the non-blank cells starting from I2 on down and paste the values in column M (starting in M2), you could use this code instead:
Sub Macro2()
' Macro2 Macro
' Keyboard Shortcut: Ctrl+l
    Sheets("Filter").Select
    Range("I2", Range("I2").End(xlDown)).Copy 'selects all cells in column I to first blank cell
    Range("M2").PasteSpecial Paste:=xlPasteValues 'pastes values in column M
    Application.CutCopyMode = False 'turns off copy buffer
End Sub

Open in new window


You'll find a lot of information here on EE about these methods, but experience is the best teacher.

-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.