Simple task but cant find a solution

[katralic]

I'm new to macros. Only about 2 months into it but I've been able to do pretty much everything I've wanted except 1.
Heres the setup
cell A1 this cell has a cell address in it
cell A2 this cell has a number in it

example
A1 R32C54
A2 536

ok I make all my macros as a button.
All I want to be able to do is hit the button and the macro will take the number from A2 which in this example is 536 and input into the cell location R32C54

Seems simple but heres the catch

The cell address listed in A1 will always be different. Once its used it will not be used again.
The next time I run it the address might be R543C128 etc

I can easily enough input the number from A2 into it once I get there but I haven't been able to figure out how to get the macro to go the address listed. It is not predictable. The cell address is dependent on other parts of the spreadsheet.
Cell A1 is just telling me where the number in A2 needs to go. I can do it manually quick enough but when I try to record the macro it doesn't work. I copy the A1 location and input it into the search bar and hit enter. it goes to the location and I type in the number from A2. Very simple. The macro will not record the pasting of A1 into the search bar. Ive read many pdf files trying to find an example and I'm beginning to think that its not possible but that's hard to believe with all the things VBA can do. HELP ME PLEASE its driving me nuts

[Aussiebear]

have a look here https://msdn.microsoft.com/en-us/lib.../ff839232.aspx. Using the Application.Goto function after you copy the cell in A2 may help you here.

[jolivanes]

If you're married to the RC notation, try this

Sub Maybe()
Dim a
    a = Split(Mid(Cells(1, 1).Value, 2, 99), "C")
    Cells(a(0) * 1, a(1) * 1) = Cells(2, 1).Value
End Sub

Or

Sub Maybe()
    Cells(Split(Mid(Cells(1, 1).Value, 2, 99), "C")(0) * 1, Split(Mid(Cells(1, 1).Value, 2, 99), "C")(1) * 1).Value = Cells(2, 1).Value
End Sub

[p45cal]

if the active sheet:
Range([INDIRECT(A1,FALSE)].Address) = Range("A2").Value
or:
Range(Application.ConvertFormula(Range("A1").Value, xlR1C1, xlA1)) = Range("A2").Value
although easy to tweak and qualify ranges.

[katralic]

thank you. I will try it out ASAP

[katralic]

I used the RC notation because I thought I needed to but its not necessary

[jolivanes]

Something like this for A1 type Addresses?

Range(Range("A1")).Value = Range("A2").Value

@p45cal
Looks like I subscribe to the "Why do it easy if you can do it difficult" ideas.
Not so. Just was not aware of the native Excel possibilities.
Thanks

[katralic]

Just to let everyone know both Jolivanes and p45cals solutions worked. I was able to understand and follow P45cals a little easier just because I am so new to macros. I am very thankful to both of you

[jolivanes]

Regarding your PM, asking to explain the 2nd macro.
Mid(Cells(1, 1).Value, 2, 99) drops the R from whatever the address is. We know that the digits between the "R" and the "C" are the Row Number
All you will have left then is 32C54
The Split, "C" does as it says, it splits the value with the letter "C" being the character where it splits at.
The (0) and the (1) are the values to the left and right of the character where it was split at.
The *1 is to convert a text value into a numerical value
If you run this macro, assuming that you still have the RC address in cell A1, you'll see what it is all about

Sub katralic()
    Dim a
    MsgBox Mid(Cells(1, 2), 2, 99)
    a = Split(Mid(Cells(1, 2), 2, 99), "C")
    MsgBox a(0)
    MsgBox a(1)
End Sub

But after all of this, remember the Split and Mid but not my way of solving your problem.
p45cal's solution, using native excel functions, is definitely the way to go.
Hope this is at least a little bit of help to you.


A Merry Christmas and a Happy, Healthy and Prosperous New Year to you and yours

[katralic]

thanks for the explanation It helps to learn different ways of doing things