Count in VBA (Excel) with a define value in an Excel Cell

0
votes

I wanted to count in my Excel VBA Macro one up, so then cell is changing +1 starting cell: Sheet2!$A1 --> next Cell Sheet2!$A2

In my Sheet 1 i have a Cell with starts by 1 and get count up by clicking a button by 1 (Cell A1 in Sheet1).

My Idea:

Sheets("Sheet2").Range("Sheet2!A Sheets("Sheet1").Range(Sheet1!A1).Value = "MY Input"

Should be like this:

Sheets("Sheet2").Range("Sheet2!A Sheet1!A1).Value = "MY Input"

But it does not work. Any Ideas ?

3
vbaexcel

3 Answers

2
votes
Sheets("Sheet2").Range("A" & Sheets("Sheet1").Range("A1").Value).Value = "My Input"

Or define the value first and use that:

Dim r as long

r = Sheets("Sheet1").Range("A1").Value

Sheets("Sheet2").Range("A" & r).value = "My Input"

I think this is what you mean, if not then please provide a clearer explanation and I'll edit the answer accordingly.

0
votes

When using double quotes you pass a string to a function (in this case the Range function), however the Range function only explicitly accepts addresses in the form of "A1" and R1C1/[R]1[C]1 like notations.

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A1").Value

Not sure though you want to achieve with the button, but why not try:

Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1

This increases the value in cell A1 by 1 each time this line is executed.

0
votes

And yet another way is to use Offset():

Sheets("Sheet2").Range("A1").Offset(Sheets("Sheet1").Range("A1")).Value = "My Input"