VBA code to replace a value with a formula

Excel Excel 2007
May 18, 2010 at 08:16:25
Specs: Windows XP
My First Post!

I have an excel spreadsheet that is dumped daily from an applet. Formating this spreadsheet is onerous, and I am automating it with Macros. For the most part, recording a macro and editing it, along with research on the web, has been able to get 85% of it done. Here is a tough one..

Sample Data:

600	1	600
100	2	200
50	2	100
50	1	50
100	2	200

The task is to 'convert' the third (right most ) column into a formula, instead of the value it presently is.

I can set the ActiveCell to the top right cell, and am running the following code:
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
to do a product using relative references.

The number of rows changes from report to report.

I've been looking for a way to 'autofill' the formula through the column of unknown length.

Any help is much appreciated.

DerbyDad03 - Is this one is up your alley?


See More: VBA code to replace a value with a formula

Report •

May 18, 2010 at 10:08:46
There's no need to set the ActiveCell - at least I don't think so based on what you've described.

Rarely do you have to Select or Activate a cell or range to perform an operation on it from within VBA. In most cases you can refer to the range directly.

e.g. instead of :

Selection.Font.Bold = True

Just use:

Range("A1").Font.Bold = True

If your recorded code contains a lot of ".Select" and "Selection." lines, you can make your code more efficient and easier to follow if you combine those lines.

Anyway, back to your question...

The first line of this code will determine the last row that contains data in Column C.

The second line will put your formula in C1:C whatever without activating any cells.

lastRw = Range("C" & Rows.Count).End(xlUp).Row
 Range("C1:C" & lastRw).FormulaR1C1 = "=RC[-2]*RC[-1]"

Report •

May 19, 2010 at 05:33:02
Thank you DerbyDad03
That works just fine. A quick follow-up Q.

Is there a ( relatively) easy way to do this with contiguous data only, rather than to the end of data in that column?

I have multiple ranges of data on that sheet, such as:

50	1	50
50	1	50
50	2	100
50	1	50
100	1	100
100	2	200

So working with contiguous data , several times, would mean I don't have to try and clean up the 'zeros' afterwards - but that is no biggie truly.

I am very thankful for your contributions here. About 1800 posts from you, averaging almost 3 a day! Kudos! Many thanks for all you do.


Report •

May 19, 2010 at 05:59:28
If you don't care about having the formula in the "empty" cells, then you could use Tools...Options and uncheck the Zero Values box so that your spreadsheet won't display 0's anywhere.

If you don't want the formula, then this code will loop through the entire Range and skip cells that don't have a value in Column C.

If you have Column headings, then start the loop at formRw = 2, or whatever row in which you need the first formula.

Option Explicit
Sub PlaceFormulas()
Dim lastRw, formRw As Integer
 lastRw = Range("C" & Rows.Count).End(xlUp).Row
  For formRw = 1 To lastRw
   If Range("C" & formRw) <> "" Then
    Range("C" & formRw).FormulaR1C1 = "=RC[-2]*RC[-1]"
   End If
End Sub

Report •

Related Solutions

May 20, 2010 at 05:43:33

Spectacular answer. I do have column headers, so I will start at 2. You have been a tremendous help. Thank you.


Report •

May 20, 2010 at 05:58:33
Glad to have been of assitance.

Report •

Ask Question