Creating a dynamic formula in vba with a variable in it

Question

Im trying to create a VBAformula that will fill excel with a formula. i've got this VBA code

Dim ws As Worksheet
Dim u As Long
Set ws = ActiveSheet

u = 9

Dim used As Range
Set used = ws.UsedRange

Dim lastRow As Integer

lastRow = used.Row + used.Rows.Count - 2
ws.Range("K4:K" & lastRow).FormulaR1C1 = "=(R4C2-R4C" & u & ")/10"

but in excel I get this formula: =($B$4-$I$4)/10

is it possible with this code to get the formula looking like this? =(B4-I4)/10 without the $ symbol?

You are using R1C1 notation with absolute addressing. That corresponds to $ signs in the A1 notation. If you want relative addressing, don't use absolute addressing in R1C1 to being with. A relative address in R1C1 has the form of R[4]C[2], but you will have to correct the numbers because they are relative to where you put the formula. — GSerg
– GSerg, Commented Dec 19, 2019 at 14:51
Sidenotes, you might be specific about which worksheet to use instead of ActiveSheet. There are also better ways to search for a last used row. — JvdV
– JvdV, Commented Dec 19, 2019 at 14:59
@GSerg. Works. I Need to make it dynamic. I didn't know how buy thanks to you, I know now — Ultiseeker
– Ultiseeker, Commented Dec 19, 2019 at 15:18

BigBen · Accepted Answer · 2019-12-19 15:41:14Z

4

I prefer .Formula over .FormulaR1C1 here:

 ws.Range("K4:K" & lastRow).Formula = "=(B4-I" & u & ")/10"

Also important: use Long instead of Integer to avoid a possible Overflow error:

 Dim lastRow As Long

EDIT:

I'm not sure exactly what the final version of the formula should be in each successive column, but perhaps you're looking for the following:

Dim i As Long
For i = 0 To 3
     ws.Range("K4:K" & lastRow).Offset(, i * 7).FormulaR1C1 = "=(R[]C2-R[]C[-" & u & "])/10"
Next

This keeps the B absolute and the rows and other column relative.

edited Dec 19, 2019 at 15:41

answered Dec 19, 2019 at 14:51

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ultiseeker Over a year ago

That works, but the I column will also chance. After column I IT will go to column R and other columns after that

BigBen Over a year ago

What is the column logic then? That is not in your original question.

Ultiseeker Over a year ago

Column logic is column +7 column and that 4 Times (in a loop i hot figured out)

BigBen Over a year ago

Does B change as well or should that be absolute?

Ultiseeker Over a year ago

B should be static

|

Collectives™ on Stack Overflow

Creating a dynamic formula in vba with a variable in it

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related