How to create a macro that goes to a specific row based on a cell value

[bill945]

Hi!

I'm just learning about excel macros and need one that will do something along the following (end result being goto cell row 25, column D in this example:

1) Cell a1 = 25 (or any # that's provided by the user in this cell)

2) Goto row value in a1, column D


Next, I'd like a slightly different macro that will go to any row and column input by the user.

1) Cell a1 = 10 (input cell for row #)

2) cell a2 = F (input cell for column)

3) Goto row a1 (=10) column a2 (=F)


I know the above macros are incredibly simple but I'm just at the moment not getting how to do it. Any assistance for this newbie is really appreciated!!

Bill
8^)

 

[Joe4]

Welcome to the Board!

First one:

Range("D" & Range("A1")).Select

Second one:

Range(Range("A2") & Range("A1")).Select

 

[bill945]

Welcome to the Board!

First one:

Range("D" & Range("A1")).Select

Second one:

Range(Range("A2") & Range("A1")).Select

\\\\\\\\\\\\\\\

Thank you soooooo much!!!!!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[TDCox]

There are of course many ways to do this, but you can also use the cells function which enables you to use values for both rows and columns.

i = range("A1").row
j = range("A1").column
Range(Cells(j,i)).select 'note that cells() has Column first, then Rows
i = activecell.row + 1
j = activecell.column + 1
k = i + 10 'add 10 onto the row
Range(Cells(j,i),Cells(j,k)).select 'select the range below the active cell down to 10 rows below

 

[TDCox]

I can't edit, so will have to repost - an extraneous "Range()" slipped in, so the 3rd line wouldn't have worked. Below is the same code encapsulated in a sub and tested, with a pass through added.

Sub test()
' Created 2018-10-20 by Timothy Daniel Cox
' example macro to call and pass a value
    Selecting ("B12")
End sub


Sub Selecting(PassedCell as string)
' Created 2018-10-19 by Timothy Daniel Cox
' last edited 2018-10-20 by Timothy Daniel Cox
' example macro to select cells from within VBA
Dim i As Long, j As Long, k As Long
    i = Range("A1").Row
    j = Range("A1").Column
    Cells(j, i).Select 'note that cells() has Column first, then Rows
    range(PassedValue).select
    i = ActiveCell.Row + 1
    j = ActiveCell.Column + 1
    k = i + 10 'add 10 onto the row
    Range(Cells(j, i), Cells(j, k)).Select 'select the range below the active cell down to 10 rows below
End Sub

There are of course many ways to do this, but you can also use the cells function which enables you to use values for both rows and columns.

i = range("A1").row
j = range("A1").column
Range(Cells(j,i)).select 'note that cells() has Column first, then Rows
i = activecell.row + 1
j = activecell.column + 1
k = i + 10 'add 10 onto the row
Range(Cells(j,i),Cells(j,k)).select 'select the range below the active cell down to 10 rows below

 

[bill945]

Thank Timothy! I'm going to try your code out right now 8^)


\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

I can't edit, so will have to repost - an extraneous "Range()" slipped in, so the 3rd line wouldn't have worked. Below is the same code encapsulated in a sub and tested, with a pass through added.

Sub test()
' Created 2018-10-20 by Timothy Daniel Cox
' example macro to call and pass a value
    Selecting ("B12")
End sub


Sub Selecting(PassedCell as string)
' Created 2018-10-19 by Timothy Daniel Cox
' last edited 2018-10-20 by Timothy Daniel Cox
' example macro to select cells from within VBA
Dim i As Long, j As Long, k As Long
    i = Range("A1").Row
    j = Range("A1").Column
    Cells(j, i).Select 'note that cells() has Column first, then Rows
    range(PassedValue).select
    i = ActiveCell.Row + 1
    j = ActiveCell.Column + 1
    k = i + 10 'add 10 onto the row
    Range(Cells(j, i), Cells(j, k)).Select 'select the range below the active cell down to 10 rows below
End Sub