excel vba convert string to range

Question

I am trying to run a macro on 3 different ranges, one after another. Once the range is selected, the code works just fine (where variables F and L are defined). I would like to set r1-r3 as Ranges I need and then use a string variable to concatenate the range numbers together. This code works, but doesn't provide the starting and ending row number in the range selected. This is vital because it tells the "TableCalc" macro when to start and stop the code. I would then like to move on to the next range. Thanks for your help.

Sub TestRangeBC()

WS.Select

Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim rngx As String
Dim num As Integer
Dim rng As Range

Set r1 = WS.Range("ONE")
Set r2 = WS.Range("TWO")
Set r3 = WS.Range("THREE")

For num = 1 To 3
    rngx = "r" & num
    Set rng = Range(rngx)

    Dim F As Integer
    Dim L As Integer

    F = rng.Row + 1
    L = rng.Row + rng.Rows.Count - 2
    Cells(F, 8).Select

    Do While Cells(F, 8) <> "" And ActiveCell.Row <= L

        'INSERT SITUATIONAL MACRO
        Call TableCalc
        WS.Select
        ActiveCell.Offset(1, 0).Select
    Loop
Next num

End Sub

Answer 1

This is not the answer (as part of your code and what you are trying to achieve is unclear yet), but it is a "cleaner" and more efficient way to code what you have in your original post.

Option Explicit

Dim WS              As Worksheet

Your original Sub shorten:

Sub TestRangeBC()

' chanhe WS to your Sheet name
Set WS = Sheets("Sheet1")

Call ActiveRange("ONE")
Call ActiveRange("TWO")
Call ActiveRange("THREE")

End Sub

This Sub gets the Name of the Named Range (you set in your workbook) as a String, and sets the Range accordingly.

Sub ActiveRange(RangeName As String)

Dim Rng                 As Range
Dim F                   As Integer
Dim L                   As Integer
Dim lRow                As Long

With WS
    Set Rng = .Range(RangeName)

    ' just for debug purpose >> to ensure the right Range was passed and set
    Debug.Print Rng.Address

    F = Rng.Row + 1
    L = Rng.Row + Rng.Rows.Count - 2

    lRow = F

    ' what you are trying to achieve in this loop is beyond me
    Do While .Cells(F, 8) <> "" And .Cells(lRow, 8).Row <= L
        Debug.Print .Cells(lRow, 8).Address
        'INSERT SITUATIONAL MACRO
       ' Call TableCalc
        ' not sure you need to select WS sheet again
        WS.Select
        lRow = lRow + 1
    Loop
End With

End Sub

What are you trying to test in the loop below, what are the criteria of staying in the loop ?

Do While Cells(F, 8) <> "" And ActiveCell.Row <= L

Answer 2

it's really hard to tell what you may want to do

but may be what follows can help you clarifying and (hopefully) doing it!

first off, you can't "combine" variable names

So I'd go with an array of named ranges names (i.e. String array) to be filled by means of a specific sub:

Function GetRanges() As String()
    Dim ranges(1 To 3) As String

    ranges(1) = "ONE"
    ranges(2) = "TWO"
    ranges(3) = "THREE"
    GetRanges = ranges
End Function

so that you can clean up your "main" sub code and keep only more relevant code there:

Sub TestRangeBC()
    Dim r As Variant
    Dim ws As Worksheet

    Set ws = Worksheets("Ranges") '<--| change "Ranges" to your actual worksheet name

    For Each r In GetRanges() '<--| loop through all ranges names
        DoIt ws, CStr(r) '<--| call the range name processing routine passing worksheet and its named range name
    Next r
End Sub

the "main" sub loops through the named ranges array directly collected from GetRanges() and calls DoIt() to actually process the current one:

Sub DoIt(ws As Worksheet, rangeName As String)
    Dim cell As Range
    Dim iRow As Long

    With ws.Range(rangeName) '<--| reference the passed name passed worksheet named range
        For iRow = .Rows(2).Row To .Rows(.Rows.Count - 2).Row '<--| loop through its "inner" rows (i.e. off 1st and last rows)
            Set cell = ws.Cells(iRow, 8) '<--| get current row corresponding cell in column "F"
            If cell.value = "" Then Exit For '<--| exit at first blank column "F" corresponding cell
            TableCalc cell '<-- call TableCalc passing the 'valid' cell as its parameter
        Next iRow
    End With
End Sub