Change cells based on their Cell Address

[gregersdk]

Hello good Excel people!

I have a, I expect, simple question regarding using absolute cell addresses to change a given cell.

I have a piece of code as stated underneath, The output is an absolute cell address: [test.xlsm]Sheet1'!R3C3

Sub Cell_adresser()

Arow = 3
Acol = 3

Sheets(1).Cells(4, 2).Value = Worksheets(2).Cells(Arow, Acol).Address(ReferenceStyle:=xlR1C1, External:=True) 'Reads cell adress
Sheets(1).Cells(4, 3).Value = Worksheets(2).Cells(3, 3).Value

End Sub

I want to list a long array of cell values and their addresses and then change their value. How do I use this absolute cell address as the reference?

"For this cell address, do something"


Hope that makes sense, Thank You


GregersDK

[Olly]

'Do something' - what exactly are you trying to do? You can reference a cell directly, depending on the method you intend to use...

[gregersdk]

So I have a table with three columns:

Cell Address | Value option one | Value option two

This table will be made from the showed Cell_Addresser (when I put it into a loop).

I want to choose a cell value between the options based on an IF statement. Something like showed below, but using the cell address read from the table instead of the Cells(1,1).Value.

Sub Address_reader()

Dim Cell_option As Integer

Cell_option = Sheets(1).Cells(1, 1).Value

If Cell_option = 1 Then
    
    Sheets(2).Cells(1, 2).Value = "Option 1"

ElseIf Cell_option = 2 Then
    Sheets(2).Cells(1, 2).Value = "Option 2"
    
End If


End Sub

The Cell references marked with red, instead of using Cell(y,x) I'd like to refer to the cell address I have printed in Sheets(1).Cells(4, 2), in general terms.

In all ways I can readily imagine, I keep referring to the cell I'm looking at and not the address I have stated in the cell's value.

I think in general my question is: How can I read and refer the address written in a cell instead of the Range(A1) or the Cells(1,1)?

[Olly]

Something like this:

Sub foo()
    Dim c As Range
    Dim iCellOption As Integer
    
    With Sheet1
        iCellOption = .Cells(1, 1).Value
        For Each c In .ListObjects("Table1").ListColumns("Cell Address").DataBodyRange
            Range(c.Text).Value = c.Offset(0, iCellOption)
        Next c
    End With
End Sub

[gregersdk]

Thank you! I'll take a look!

It seems you can just refer to addresses through the Range(c.Text).Value if I'm not mistaken, as a general thing?

But thank you for setting into context in tables too!