Solved: Need to insert formula using macro

Printable View

02-06-2009, 10:56 AM
clhare

Solved: Need to insert formula using macro

I need help inserting the following formulas into all rows of a particular column using a macro. I generally do not work in Excel at all, so I don't have any idea how to do this.

Here's the two formulas I need to insert in all rows starting with row 3:

Formula #1 -- Insert in all rows of column X
=IF(S3,"No Answer",IF(T3,"Left Message",IF(U3,"Wrong Number",IF(V3,"Reached",IF(W3,"Busy","")))))

Formula #2 -- Insert in all rows of column Y
=IF(S3,S3,IF(T3,T3,IF(U3,U3,IF(V3,V3,IF(W3,W3,"")))))

I have no clue what these formulas are even doing. I just know they exist in the original file and the macro I am updating creates a new file. I need the macro to also add these two formulas to columns X and Y of the new file.

I really appreciate any help!
02-06-2009, 11:00 AM
Bob Phillips

Personally, I am dubious about those formulae, they are checking those cells for True which I find unlikely

[vba]

Dim LastRow As Long

'this code sets LastRow, but you may want to calculate it somehow

LastRow = 20
Range("X3").Resize(LastRow - 2).Formula = _
"=IF(S3,""No Answer"",IF(T3,""Left Message"",IF(U3,""Wrong Number"",IF(V3,""Reached"",IF(W3,""Busy"","""")))))"

Range("Y3").Resize(LastRow - 2).Formula = _
"=IF(S3,S3,IF(T3,T3,IF(U3,U3,IF(V3,V3,IF(W3,W3,"""")))))"
[/vba]
02-06-2009, 12:07 PM
clhare

The macro I have to add these formulas to already has a variable that determines the last row (max_row), so would I use that instead of the lastrow variable?

Also, wouldn't the "3" in the formulas need to be changed to a variable that references the each row somehow? I don't know how to do that.
02-06-2009, 12:10 PM
Bob Phillips

Quote:

Originally Posted by clhare

The macro I have to add these formulas to already has a variable that determines the last row (max_row), so would I use that instead of the lastrow variable?

Yes, you would.

Quote:

Originally Posted by clhare

Also, wouldn't the "3" in the formulas need to be changed to a variable that references the each row somehow? I don't know how to do that.

Try it and see.
02-06-2009, 12:29 PM
clhare

I tried the code you provided and just changed lastrow to max_row. It looks like it works on all the rows. How does it do that when row 3 is specified in the code? Does the formula automatically reset to the correct row number based on which row it is in?

Sorry I don't know this, but I am totally new to Excel.
02-06-2009, 12:33 PM
Bob Phillips

It is one of Excel's smart features. If you specifiy a relative cell in a formula and load many cells with that formula, Excel will adjust each row accordingly.