Go to specific cell with address in another cell

Question

Say if I have addresses of cell in one column in excel. for example Column E in an excel has addresses of various cells in the excel (i.e.$H$5, $J$7 etc.). I am looking for a code which can select the cell mentioned in this column.

Attached is the snapshot which is the intended output of the code. However, it fills entire rows and columns with Value X in the excel.

Sub GCELL()
    Dim lastrow As Long
    Dim lastrow1 As Long

    lastrow = Sheet1.Cells(Rows.Count, 5).End(xlUp).Row  
    lastrow1 = Sheet1.Cells(Rows.Count, 3).End(xlUp).Row

    For i = 5 To lastrow
        For j = 5 To lastrow1        
            Application.Goto Reference:=Cells(i, j)
            ActiveCell.FormulaR1C1 = "X"
        Next j   
    Next i
End Sub

Show research effort, any tries, ideas. SO doesn't work in that way. — Pochmurnik, Sep 05 '19 at 05:53
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading [ask] and [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) might help you to improve your question. — Pᴇʜ, Sep 05 '19 at 06:14
@Deepak Please [edit] and put the code in your original question (and delete the comment). As you can see it is completely unreadable in comments. Also explain what went wrong (*"doesn't work"* is always a completely useless error description, because it gives us no information at all). Which errors did you get and in which line? If no errors, what did your code do instead of what did you expect it to do? — Pᴇʜ, Sep 05 '19 at 06:48

Pᴇʜ · Answer 1 · 2019-09-05T07:33:25.767

I don't get the point of the inner loop, but to answer the main question:

If cell E1 contains the text $H$5, $J$7 then you can read that value with Sheet1.Range("E1").Value and use it ass address in another range to select that Sheet1.Range(Sheet1.Range("E1").Value).Select.

Note that I used .Select only for demonstration purposes: How to avoid using Select in Excel VBA.

So the following should write an X to all cells which addresses listed in column E.

Option Explicit 

Public Sub GCELL()
    Dim lastrow As Long
    lastrow = Sheet1.Cells(Rows.Count, 5).End(xlUp).Row  

    Dim i As Long
    For i = 5 To lastrow
        Sheet1.Range(Sheet1.Cells(i, "E").Value).Value = "X"
    Next i
End Sub

Go to specific cell with address in another cell

1 Answers1