asked on

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

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

Or my article on debugging which applies to both VBA and VB6.

Glenn Ray

^^^Yep. Sorry, I forgot about your very good article, Martin. :-)

Rob Henson

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

GPSPOW

ASKER

Thanks

I was able to figure out where the error was.

Glen

Glenn Ray

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

GPSPOW

ASKER

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

Glenn Ray

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