excel vba - insert a formula using variables

Question

Hi I want to insert formula into a cell by determining variables for each value.

Eg.

=G5/D5

So in cell

Sheets("Sheet1").cells(LastRow + 2, 2).value = "=columnletter1 + (lastrow + 2)/columnletter2 + (lastrow + 2)

I find the columnletter is G by determining if the column name is X and the row is LastRow + 2. How do I write so that I get "=G5/D5" returned into the cell?

chris neilsen · Accepted Answer · 2012-08-27 07:30:32Z

3

Not sure if I understand your requirements exactly, I think you mean:

columnletter1 is a string, = G
columnletter2 is a string, = D
lastrow is a long, = 3

To set a formula, construct a string and write to the .Formula property

    Sheets("Sheet1").cells(LastRow + 2, 2).Formula = _
    "=" & columnletter1 & lastrow + 2 & "/" & columnletter2 & lastrow + 2

answered Aug 27, 2012 at 7:30

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

Comments

Kevin Brown-Silva · Accepted Answer · 2014-12-02 02:31:04Z

1

For your matrix calculations below, the given code including the formula with variables may be helpful.

For ROW= 0 To K - 1
    For COL= 0 To J - 1
        Range("G5").Offset(ROW, COL).Select
        ActiveCell.FormulaR1C1 = _
        "=SUMIFS(R2C5:R500C5,R2C4:R500C4,RC[" & -COL- 1 & "],R2C2:R500C2,R[" & -ROW- 3 & "]C,R2C3:R500C3,R[" & -ROW- 2 & "]C)"

    Next
    COL= COL + 1

Next
ROW= ROW + 1

edited Dec 2, 2014 at 2:31

Kevin Brown-Silva

41.8k42 gold badges207 silver badges243 bronze badges

answered Dec 2, 2014 at 1:59

AHMET NIDA

112 bronze badges

Collectives™ on Stack Overflow

excel vba - insert a formula using variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related