How do I test whether a string is a valid cell reference?
in [Programming]
|
From: JT on 16 Apr 2010 04:25 On Apr 16, 1:43 am, JLatham <JLat...(a)discussions.microsoft.com> wrote: > Glad our ideas gave you an idea. Didn't realize you wanted to do it in a > worksheet cell, so I gave up the VBA code. > > > > "JT" wrote: > > On Apr 15, 1:29 pm, JLatham <JLat...(a)discussions.microsoft.com> wrote: > > > Rather than trying to parse the text, you could use something like this: > > > > 'near the beginning of your process > > > Dim testForRange As Range > > > dim testAddress As String > > > > ...in here you get the string and > > > ...put it in the example testAddress variable > > > ... now you test it using error trapping > > > ... assumes the range is to be on the current active sheet > > > On Error Resume Next > > > Set testForRange = ActiveSheet.Range(testAddress) > > > If Err <> 0 then > > > 'had an error, presumed invalid address string > > > MsgBox testAddress & " is not a valid range address." > > > Err.Clear ' clear the error > > > End If > > > On Error GoTo 0 ' reset error trapping > > > > "JT" wrote: > > > > I would like to develop a way of testing whether a string entered by a > > > > user can be used by excel to define a range of cells within a sheet.. > > > > > For example: "A1:C6" or "A:Z" would be OK but "iljfneklj" would not.. > > > > > I'd like to have ways of doing this both in VBA and also using an > > > > excel formula. > > > > > Are there any ideas out there? > > > > > Thanks > > > > > John > > > > .- Hide quoted text - > > > > - Show quoted text - > > > Thanks both > > > I have now also applied this principle to achieve the same using an > > excel formula: > > > Where C36 contains a user input cell reference: > > =IF(C36="","OK",IF(ISERROR(ROWS(INDIRECT(C36))),"INVALID CELL > > REFERENCE","OK")) > > > John > > .- Hide quoted text - > > - Show quoted text - I was looking to do both, so thanks! |