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, Dec 19 '19 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](https://stackoverflow.com/q/11169445/9758194). — JvdV, Dec 19 '19 at 14:59
@GSerg. Works. I Need to make it dynamic. I didn't know how buy thanks to you, I know now — Ultiseeker, Dec 19 '19 at 15:18

BigBen · Accepted Answer · 2019-12-19T15:41:14.853

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 '19 at 15:41

answered Dec 19 '19 at 14:51

BigBen

46,229
7
24
40

That works, but the I column will also chance. After column I IT will go to column R and other columns after that – Ultiseeker Dec 19 '19 at 14:59
What is the column logic then? That is not in your original question. – BigBen Dec 19 '19 at 15:01
Column logic is column +7 column and that 4 Times (in a loop i hot figured out) – Ultiseeker Dec 19 '19 at 15:10
Does `B` change as well or should that be absolute? – BigBen Dec 19 '19 at 15:10
B should be static – Ultiseeker Dec 19 '19 at 15:13
@Ultiseeker see the edit. I'm not sure if that's what you're going for. It keeps the $B absolute and the columns and rows relative. – BigBen Dec 19 '19 at 15:41
Works as well. GSerg also provided a great answer. – Ultiseeker Dec 19 '19 at 15:50

Creating a dynamic formula in vba with a variable in it

1 Answers1