.Address from cell back to Range VBA

[dmenegr]

Hi All,

I have to macros I'm working with the first take a user set range and prints the . Address to a cell.

The second Macro I'm trying to get to look at that cell and pull out the range. So is there an opposite fuction to . Address that will convert excel format to a VBA Range format. Ex A cell with $A$1:$A$2 to Range("A1",A2")

Macro 1

For j = 1 To x
c = 1
    For i = 1 To ws.Range("AD" & rc).Value
    ws.Range("AE" & rc).Value = Application.InputBox(Prompt:="What is the Heading of Data set #" & c & " Table " & tc & " This entry may repeat", Type:=8)
    ws.Range("AF" & rc).Value = Application.InputBox(Prompt:="What is the Data set title of Data set #" & c & " Table " & tc & " If Header is data set the Data Title hit Cancel", Type:=8)
    Set a = Application.InputBox(Prompt:="What is the Range of Data set #" & c & " Table " & tc, Type:=8)
    ws.Range("AG" & rc).Value = a.Address
    rc = rc + 1
    c = c + 1
    Next i
tc = tc + 1
rc = rc + 11 - i
Next j

Macro 2

'A lot of code here but I want to be able to set multiple ranges to the ranges list in the cell values. 
rng(2) = sh(0).Range("AG2").Value

 

[rjwebgraphix]

How do you know which worksheet the reference in on and which worksheet the reference refers to?

I didn't know and didn't want to guess.