.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

 

[Norie]

Why not just use Range?

Set rng= sh(0).Range(sh(0).Range("AG2").Value)

Note I'm assuming sh(0) is a worksheet reference and both ranges are on that worksheet.

 

[dmenegr]

I am making a macro for a template that uses input boxes to let the user record the ranges. However I do not want them to have to go through and reset the range each time

and no sh(0) and rng are not on the same sheet. rng uses the set range to look through each worksheet and pull the data onto a summary sheet.

Set rng= sh(0).Range(sh(0).Range("AG2").Value)

Note I'm assuming sh(0) is a worksheet reference and both ranges are on that worksheet.[/QUOTE]

 

[Norie]

Which worksheet is the range on then?

 

[rjwebgraphix]

If I understand the need correctly. You have a range as a string, in a cell and want to use that range in VBA. Something like this....

Sub test2()

Dim RangeInCell As String
Dim Rng As Range
Dim cel As Range

RangeInCell = Cells(1, "B").Value
Set Rng = Range(RangeInCell)

For Each cel In Rng
    MsgBox cel
Next cel

End Sub

Excel Workbook
	A	B
1	2	$A1:$A2
2	5
Sheet

 

[dmenegr]

Yes, that is what I needed this worked thank you.

If I understand the need correctly. You have a range as a string, in a cell and want to use that range in VBA. Something like this....

Sub test2()

Dim RangeInCell As String
Dim Rng As Range
Dim cel As Range

RangeInCell = Cells(1, "B").Value
Set Rng = Range(RangeInCell)

For Each cel In Rng
    MsgBox cel
Next cel

End Sub

*	A	B
1	2	$A1:$A2
2	5	*

<COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Norie]

Eh, that's basically what I suggested.

 

[rjwebgraphix]

Eh, that's basically what I suggested.

It's exactly the same thing, just split into two lines so its easier to read for the novice and ability to specify sheets without confusion of what should go where.

RangeInCell = Sheets("Sheet3").Cells(1, "B").Value
Set Rng = Sheets("Sheet1").Range(RangeInCell)

is just easier to read than

Set Rng = Sheets("Sheet1").Range(Sheets("Sheet3").Cells(1, "B").Value)

I know a lot of people like doing things in the fewest lines possible, but I've found it often harder to read from a novice perspective. Not to mention, the extra variable gives you the added luxury of reusing it's shortened variable version multiple times and only changing the cell reference in one location vs. multiple locations if the need should arise.

Its more for the second reason that I'm more inclined to put values into a variable than mix them in the same line of code. Although, the more experienced might tell me that less is more.

 

[Norie]

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

 

[needhelp2]

Why not just use Range?

Set rng= sh(0).Range(sh(0).Range("AG2").Value)

Note I'm assuming sh(0) is a worksheet reference and both ranges are on that worksheet.

Norie ! you are great as always . God Bless you.