CODEWITHSUNDEEP
excelvba
Ultiseeker
Ultiseeker

Reputation: 181

Creating a dynamic formula in vba with a variable in it

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?

Upvotes: 2

Views: 3021

Answers (1)

BigBen
BigBen

Reputation: 50162

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.

Upvotes: 4

Related Questions