Excel IF cell reference formula being changed when recorded in VBA

0
votes

I have a macro that works fine. I'm adding a formula to it that describes the status of the workbook for someone else to review. When I record the macro of implementing the formula into the sheet, VBA records it using relative references even though I do not have the relative references button selected. The relative references do not point correctly so I need to fix it. I checked this post (adding a dynamic cell reference in vba) and am now thinking that I should adjust the formula with some VBA reference code but I'm not sure if what the post is using is suitable for me. Am I going in the right direction?

Excel Formula:

=IF(Selections!K2="","Not prepped","Prepped")

When recorded into VBA:

ActiveCell.FormulaR1C1 = _
    "=IF(Selections!R[-41]C[-1]="""",""Not prepped"",""Prepped"")"

What I need in VBA code:

ActiveCell.FormulaR1C1 = _
    =IF(Selections!K2="""",""Not prepped"",""Prepped"")
1
vbaexcelreferenceexcel-formula
Do you mean ActiveCell.Formula = "=IF(Selections!K2="""",""Not prepped"",""Prepped"")" – area9
Is there a specific cell you want the message to populate in? – Matt Cremeens
@area9 I do, I edited my post. Matt Cremeens I want it to populate in cell L5 – plankton

1 Answers

2
votes

ok, then is this your answer:

dim rng as range

set rng = thisworkbook.sheets("Sheet1").range("L5")
rng.Formula = "=IF(Selections!K2="""",""Not prepped"",""Prepped"")"