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 ?
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.
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.
And yet another way is to use Offset():
Sheets("Sheet2").Range("A1").Offset(Sheets("Sheet1").Range("A1")).Value = "My Input"
