Extracting first (cell)adress in VBA from string

Willem!

Board Regular
Joined
Mar 6, 2009
Messages
139
Hi!

I want to extract the first (cell)adress from a string (which is the string of a range) in VBA.

Example:

strRange = "$P$2:$T$4"

strCell = *some function/code* = "$P$2"

Can this be done in a simple way?

TIA
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi,

How would the "$P$2:$T$4" string be assigned to your strRange variable in the actual code? Using the range object's address property? What do you want to do with the first cell's address once you have it?
 
Upvote 0
Code:
strCell = Split(strRange, ":")(0)
would work.
 
Upvote 0
Hi,

How would the "$P$2:$T$4" string be assigned to your strRange variable in the actual code? Using the range object's address property? What do you want to do with the first cell's address once you have it?


Hi Colin, yes, I by using the object's address property I assign a value to strRange

On my sheet a user can select with a RefEdit-control a certain range from which I need the upperleft-, and the lowerright-corner. I need these for a VBA-function which creates a giant matrix (since my matrix has more than 73 variables I cannot use array-functions, so I need UDF's).

Thanx for your help! :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top