Dynamic formula in cell with vba

[twinpowr]

Hi,

I have just read a post in which it describes how to put a formula in a cell with vba. Which works great, but how do we make that formula dynamic and follow on from the previous cell example:-

=IF(A2="","",IF(F2<>"",A2+0,A2+7))

so when im entering data from a userform and moving down through the rows, then next row should then be

=IF(A3="","",IF(F3<>"",A3+0,A3+7))

and onwards down the rows.

Thanks in advance for any help

Ally.

 

[Weaver]

Instead of updating the formula, use .formulaR1C1

Then you make your references relative to the row/column you're working with

say = A1 * 2 in column C

formula = "=A1*2"

formulaR1C1 = "=RC1*2" (absolute column, or $A2)

or

formulaR1C1 = "=RC[-1] (relative column, or A2)

To effectively edit your formula to suit, I'd need to know which column it was in and/or if the references are absolute or relative

Alternatively, you can select a cell with the formula in and in the vb immediate window, type

?selection.formulaR1C1

HTH

 

[GlennUK]

Hi,

I have just read a post in which it describes how to put a formula in a cell with vba. Which works great, but how do we make that formula dynamic and follow on from the previous cell example:-

=IF(A2="","",IF(F2<>"",A2+0,A2+7))

so when im entering data from a userform and moving down through the rows, then next row should then be

=IF(A3="","",IF(F3<>"",A3+0,A3+7))

and onwards down the rows.

Thanks in advance for any help

Ally.

Don't fill your cells one at a time. If you fill a range with that formula it will adjust automatically ( as they are relative references ). Like:

Range("B2:B99").Formula = "=IF(A2="""","""",IF(F2<>"""",A2+0,A2+7))"

 

[twinpowr]

hi guys thanks for your comments.

the reason I need to enter the formulas one row at a time
is that the data in this worksheet gets picked up and populated into
a listbox using lastrow to determine the size of the datalist.

I tried using eg. ("g3:g" & lastrow) in the vba code but that did not
seem to work. I will give some your code a go and see how that
works.

Thanks again for your help it's very much appreciated.

Ally.

 

[GlennUK]

I tried using eg. ("g3:g" & lastrow) in the vba code but that did not
seem to work.

If you are still having trouble, post the entire line of code.

 

[twinpowr]

Hi Glenn,


here is the full line i am trying to use

worksheets("Data").Cells(lastrow, 7).Value = "=IF("A2:a" & lastrow) ="""",""""=IF("F2:F" & lastrow)<>"""","A2:a" & lastrow)+0,A2+7))"

any ideas would be great

thanks in advance

Ally.

 

[GlennUK]

Your original formula was:

=IF(A2="","",IF(F2<>"",A2+0,A2+7))

... which translated into a string for VBA I quoted as

"=IF(A2="""","""",IF(F2<>"""",A2+0,A2+7))"

Your latest formula looks like neither.

It seems you are trying to put something into a single cell now ... and are using .Value instead of .Formula ... oddly.

What formula are you trying to apply to what range?

 

[twinpowr]

Hi,

yea i think i confused my self in the process of copy and pasting across

the actual formula should be:-

=IF(A2="","",IF(F2<>"",A2+0,A2+7))

which should be relative down the entire range of g2 in the "Data" worksheet down to the lastrow. i can enter a single forumla, but this then is not relative to the one above and remains as the same as entered.

its getting it to be relative so that when the user enters their data in the userform it calculates relative to the last line of data entered.

Thanks, sorry for the confusion.

Ally.

 

[GlennUK]

i can enter a single forumla, but this then is not relative to the one above and remains as the same as entered.

.... no, that is not the case. I said so earlier.

Range("G2:G" & lastrow).Formula = "=IF(A2="""","""",IF(F2<>"""",A2+0,A2+7))"

 

[twinpowr]

thanks again Glenn.

Not with it today.

i am getting a type mismatch error using the code above.

thanks for your help. seem to be causing confusion today.

thanks

Ally.