GO TO a cell address with VBA

[djradam]

Hello! I usually come here for help when I can't find what I'm looking for, so maybe someone can help. Most of the questions I've seen are asking how to get a cell address. I would like to go TO a cell having the address.

I have a cell that contains a cell address using the the =CELL("address".... formula. I'm creating a macro and I want another cell value to go TO that address. So for example, I want this to happen (I know this doesn't work, but hopefully you can see what I'm trying to explain):

cell value: '[macro working file.xlsm]Innovation'!$B$13

code:
range('[macro working file.xlsm]Innovation'!$B$13).select

so I guess I'm asking if there's a way to take that whole address and use that. If not, I can break it up into book, sheet, and cell reference. with some some .value manipulations.


Thank you so much,
Robert

 

[Kenneth Hobson]

See =Indirect.

 

[djradam]

Ken,

thank you for your quick response, but from what I understand INDIRECT GRABS information from a cell address, i want to PUT information to that cell address, kind of like a "reverse-indirect" I guess


I saw a function used on here, hyperlink, but I don't think it can use that whole value string , '[macro working file.xlsm]Innovation'!$B$13 .... I think it uses this part '[macro working file.xlsm]Innovation' , B13 . I'm trying to come up with something where I don't have to really manipulate the original value '[macro working file.xlsm]Innovation'!$B$13



maybe another example of what i'm trying to do is this:
This is all the information I have "ABC" and '[macro working file.xlsm]Innovation'!$B$13
I want to put "ABC" into that cell, but not manually (as in me going to that book, sheet, cell then typing "ABC")
i want something like (and i know this doesn't work)
cell('[macro working file.xlsm]Innovation'!$B$13).value = "ABC"

and again, this will be in VBA

 

[Kenneth Hobson]

Without the full path, you would have to assume that the workbook is in the same folder as the one open. This may not be the case.

If the other workbook was open, you can do it easy enough.

Otherwise, I would probably use an ADO method to insert it or quickly open it (providing that I knew the path), insert the value and then close it.

So, what is your pleasure?

 

[djradam]

Ken,

This is all in the same workbook (just many tabs). I ended up just extracting the sheet and cell names since this is all in the same book.

This is pretty much what I have done and it does the job:

Dim book, sheet, cell, currentSheet As String
Dim mary As Variant

Sheets("INPUT SUMMARY").Select
Application.Goto Reference:="R4C2"

Do While 1 = 1

If IsEmpty(ActiveCell.Offset(0, -1).Value) Then
GoTo 0
End If

If IsEmpty(ActiveCell.Offset(0, 1).Value) Or IsEmpty(ActiveCell.Value) Or IsEmpty(ActiveCell.Offset(0, 4).Value) Then
GoTo 1
End If

If Application.WorksheetFunction.IsText(Range(ActiveCell.Offset(0, 1).Address)) Then
GoTo 1
End If


If ActiveCell.Offset(0, -1).Value = "open" Then
mary = ActiveCell.Offset(0, 1).Value
sheet = Mid(ActiveCell.Offset(0, 4).Value, InStr(ActiveCell.Offset(0, 4).Value, "]") + 1, InStr(ActiveCell.Offset(0, 4).Value, "!") - 2 - InStr(ActiveCell.Offset(0, 4).Value, "]"))
cell = Mid(ActiveCell.Offset(0, 4).Value, InStr(ActiveCell.Offset(0, 4).Value, "!") + 1, 100)
currentSheet = ActiveSheet.Name

Sheets(sheet).Range(cell).Value = mary

End If

1
ActiveCell.Offset(1, 0).Select

Loop

0
End Sub