Excel vba with variables and formula

Question

I need some help Regarding VBA. I have a excel workbook with 2 sheets. In sheet 1, J1 cell I have a value which can vary. In sheet 2 I am running the following code to add values to cells.

Dim wb As Workbook
Dim nws, ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")
Set nws = wb.Sheets("Sheet2")

ws.Unprotect

With nws

.Cells(1, 2).Value2 = "0"

For i = 1 To cnt1

.Cells(j, 2).Value2 = "=.Cells(i, 2).Value2+1"

Next i

End With

I am failing in the part

=.Cells(i, 2).Value2+1

I know I'm writing it wrong. But that's the way I want the code to work.

Please excuse if I'm not putting the question correctly. Kindly help.

Andy G · Accepted Answer · 2014-05-29 10:02:12Z

1

.Cells(j, 2).Value2 = "=.Cells(i, 2).Value2+1"

remove the double-quotes and the extra equals. That makes it a string, rather than evaluating the expression.

answered May 29, 2014 at 10:02

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

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

2 Comments

slyclam Over a year ago

Just tried running it without quotes. .Cells(j, 2).Value2 = =.Cells(i, 2).Value2+1 Gives syntax error.

Andy G Over a year ago

..and the extra equals sign.

Collectives™ on Stack Overflow

Excel vba with variables and formula

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related