What is the most efficient way to define a startin

Question

I have a number of macros now that I use across a variety of different workbooks. For the most part, they loop through each cell in a column and carry out some action.

Because I'm using different books, typically the data is in different places, so I usually start my macros with some kind of user input to tell the macro where the start point is (what row and what column).

I've put a code below that I typically use which allows the user to select a cell, then uses left and mid functions to dissect the address. This works, but I'm not sure it's the best way to do what I want.

My question is, what is the most efficient way of doing this? The challenge I've had so far is I seem to need the row number by itself as an Integer so I can use it for a counter, hence why I use the Right and Mid functions.

Sub test()

Set myCell = Application.InputBox( _
prompt:="Select a cell", Type:=8)

i = Right(myCell.Address, 1)
col = Mid(myCell.Address, 2, 1)

Do

    Cells(i, col).Offset(0, 1).Value = "Test"
    i = i + 1

Loop Until Cells(i, col).Value = ""

End Sub

Answer 1

Here you're extracting row/column information from myCell, just to re-combine it again later to re-create the same range:

i = Right(myCell.Address, 1)
col = Mid(myCell.Address, 2, 1)
Do
    Cells(i, col).Offset(0, 1).Value = "Test"
    i = i + 1
Loop Until Cells(i, col).Value = ""

would be shorter as:

Do While myCell.Value <> ""
    myCell.Offset(0,1).Value = "Test"
    Set myCell = myCell.Offset(1, 0)
Loop

No need to extract row and column from myCell

Might also be a good idea to check the value of myCell.Count (or .CountLarge) to make sure your user only picked a single cell.