Solved

Copy a formula in A2 down column.

Posted on 2013-12-20
4
486 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 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
EXCELent!!  -R-
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now