Improve company productivity with a Business Account.Sign Up

x
?
Solved

Copy a formula in A2 down column.

Posted on 2013-12-20
4
Medium Priority
?
555 Views
Last Modified: 2013-12-20
What is the VBA code to copy a formula down a column (is case A) starting at A2, and copying it down as long as there is a value in the cell to its right?  Then after that Copy>SpecialPaste>Values and number formats, so the formula is gone?

Assuming it starts with A2 being the active cell.

Please advise and thanks. -R-
0
Comment
Question by:RWayneH
  • 2
4 Comments
 
LVL 55

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39732298
Hi,

pls try
Set myRange = Range(Range("A2"), Range("A" & Range("B2").End(xlDown).Row))
Range("A2").AutoFill Destination:=myRange
myRange.Copy
myRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

Open in new window

Regards
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39732306
RWH,

You're likely going to get a few methods of doing the same. Here's mine:
 With Range("A2")
  If Len(.Offset(0, 1).Value) > 0 And Len(.Offset(1, 1).Value) > 0 Then
   .Copy .Resize(.Offset(0, 1).End(xlDown).Row - .Row + 1, 1)
  .Resize(.Offset(0, 1).End(xlDown).Row - .Row + 1, 1).Value = .Resize(.Offset(0, 1).End(xlDown).Row - .Row + 1, 1).Value
  End If
 End With

Open in new window

Matt
0
 

Author Closing Comment

by:RWayneH
ID: 39733162
EXCELent!!  -R-
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39733178
EXCELent!!  -R-
:D

By the way, if this macro might ever be run when there is only one row of data in the column next to it, it will fill the formula down through every row in the sheet (1m+). If that is even a possibility, you may want to add something to check for that. If it is only run manually, and you wouldn't do it with only one row, then there's no need.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

606 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