VBA Formula

[zinah]

Hi,

I have below formulas and I need someone to help me writing it as VBA formula:

=IF($Y$16 = "by Loc", INDIRECT($AC$14), $Z$15)

=IF($Y$16 = "by Loc", IF(ROW(INDIRECT($AC$14)) + COUNTA($Y$18:$Y18) > ROW(INDIRECT($AC$15)), "", OFFSET(INDIRECT($AC$14), COUNTA($Y$18:$Y18), 0)),
IF($Z$15 + COUNTA($Y$18:$Y18) > $AA$15, "", $Z$15 + COUNTA($Y$18:$Y18)))

Thanks

 

[Sahak]

Hi Zinah

Try This

Sub EnterFormula()
    Range("A1") = "=IF($Y$16 = ""by Loc"", INDIRECT($AC$14), $Z$15)"
    Range("A2").Formula = "=IF($Y$16 = ""by Loc"", IF(ROW(INDIRECT($AC$14)) + COUNTA($Y$18:$Y18) > ROW(INDIRECT($AC$15)), """", OFFSET(INDIRECT($AC$14), COUNTA($Y$18:$Y18), 0)),IF($Z$15 + COUNTA($Y$18:$Y18) > $AA$15, """", $Z$15 + COUNTA($Y$18:$Y18)))"
End Sub

Remember it shoul be """" not "", four Quotation marks, not two, always double

 

[zinah]

Hi
Sahak

Thank you so much for your help, I need more favor please with below formula, I have range [X18:X42] need to update this formula to manage populating all the formulas starting from row 18 to 42, how can I do that?

.Formula = "=IFERROR(1-(AD18/AC18)," & Chr(34) & Chr(34) & ")"

 

[Michael M]

USe

Range("X18:X42").Formula = "=IFERROR(1-(AD18/AC18),IFERROR(1-(AD18/AC18),""""))"

 

[Sahak]

Hi Zinah,

I'm sorry for late reply, I just came home. I'm happy that I could help you. Your second question also answered correctly & this time by Michael M

 

[zinah]

Thank you
That really helped, but when I wanted to turn the formula to value by using below, it didn't work, can you please advise what should I do?

.Value = .Value

 

[zinah]

No worries Sahak

I really appreciate your time and support, I'm new to VBA and trying my best to learn this amazing language , I have another formula which can give similar result as the one you suggested but I need to add .Row, however, I'm not sure what I'm missing:

.Formula = "=IFERROR(1-(AD" & .row "& /"AC & .row)," & Chr(34) & Chr(34) & ")"