Target Address

Question

I have an address listed in cell C1 (ex. E6).  I would like some vba that would select that cell stated in C1.  So everytime the target address in C1 changes that cell address will be selected.

Thanks

Answer 1

I have formulas that chang the "cell reference" in cell C1.  Basically I want code to run that will check the cell location stated in C1 and select that cell.  It doesn't need to see that the cell has changed, just look at cell C1 when the code runs.

 

Thanks

Hi,

Either of these 2 lines in your code will read c1 and select the addres of the text in that cell

Application.Goto Range(Range("C1").Text)

Range(Range("C1").Text).Select

Unless you are very confident that C1 will always contain a valid address you should wrap either of the above lines with

on error resume next

on error goto 0

Answer 2

I have code that would trigger that part.  I would like for it to go in my regular module and I can call it from within there.

Thanks

Answer 3

Thanks, is there anyway to put this code in a module and not the sheet object?

 

It could be re-written to go in a general module but if you want it to run automatically then we would still need to use the change event to call it.

Answer 4

Thanks, is there anyway to put this code in a module and not the sheet object?

Answer 5

Hi,

Right click the sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Address = "$C$1" Then

Application.EnableEvents = False

On Error Resume Next

Range(Target.Text).Select

On Error GoTo 0

Application.EnableEvents = True

End If

End Sub