splitting string

[rossbritton26]

Hi,

i have a cell reference (cellref) which is a string from an input box,
what i want to do is to split the cell reference into the column reference and the row reference. The following code works until the row ref goes above 9 and the column ref goes beyond Z. Any ideas??

    Col = Format(Left$(cellref, 1))

    Row = Format(Right$(cellref, 1))

 

[Ruddles]

I would probably do:-

col = Left(Range(cellref).Address(1, 0), InStr(Range(cellref).Address(1, 0), "$") - 1)
row = Range(cellref).row

assuming col is String and row is Long or Integer.

(In fact I would never give my VBA variables the same name as an Excel object, like Row, to avoid the possibility of confusion. (VBA copes, humans don't always!) I would have called it iRow or some other variation.)

 

[pgc01]

Hi Ross

Is the address absolute or relative?



P. S. I also think that it's bad practice to use the name of a property as the name of your variable.

Since the Row number is integer you could for ex. use:

Dim lRow as Long
 
lRow = ...

 

[Rick Rothstein]

Others have told you about using Row as a variable name. Here is another method to get what you asked for...

TheRow = Range(cellref).Row

TheColumn = Split(Range(cellref).Address, "$")(1)

 

[rossbritton26]

Thanks for the replies,

Ruddles i have used your idea as it worked perfectly, thank you!!

I have also changed my variable names!! Thank you both for the advice

 

[Ruddles]

Be sure to test it against a wide variety of different inputs all the way from A1 to the row and column limits of your worksheet, with and without the $ before the row and/or column reference.

(Testing against a wide variety of input data is always wise when receiving code from another person. Never assume because a piece of code works in limited circumstances, that it will work in all scenarios.)