?
Solved

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

Posted on 2014-08-14
8
Medium Priority
?
1,051 Views
Last Modified: 2014-08-15
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
0
Comment
Question by:GPSPOW
8 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40262153
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
 
LVL 51

Expert Comment

by:Martin Liss
ID: 40262180
Or my article on debugging which applies to both VBA and VB6.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40262229
^^^Yep.  Sorry, I forgot about your very good article, Martin. :-)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 35

Expert Comment

by:Rob Henson
ID: 40262893
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
 

Author Closing Comment

by:GPSPOW
ID: 40263408
Thanks

I was able to figure out where the error was.

Glen
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40263817
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
 

Author Comment

by:GPSPOW
ID: 40263837
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40263908
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question