CODEWITHSUNDEEP
vbaexcel
RobK
RobK

Reputation: 207

Return .cells range in Excel formula

I'm trying to get a script to find the appropriate column and create a formula with that cells "name" in it. This is the script:

'Search for value
Dim i As Integer
i = 4
    Do Until Cells(9, i).Value = ddLeveranciers.Value Or Cells(9, i).Value = ""
        i = i + 1
    Loop

'Add formulas
Range("D5").Formula = "=IF(" & Cells(15, i) & "<>"""",D4*" & Cells(15, i) & ","""")"

This now returns the formula "=IF(1.23<>"",D4*1.23,"")", 1.23 being the value of cells(15,i). I would like the script to return (for example) "=IF(D15<>"",D4*D15,"")". How do I do that?

Upvotes: 1

Views: 71

Answers (1)

luke_t
luke_t

Reputation: 2975

You can use the .Address property.

Range("D5").Formula = "=IF(" & Cells(15, i).Address & "<>"""",D4*" & Cells(15, i).Address & ","""")"

For example:

MsgBox(Cells(1,1).Address)

Would return $A$1

Upvotes: 3

Related Questions