Variables in a range

[KDS14589]

I have this code, that works fine, BUT the range(“E5:E1000”) and Range("T5:T1000") are set to fixed ranges [5 to 1000].

Dim R As Long

R = WorksheetFunction.Index(ShGE03.Range("E5:E1000"), WorksheetFunction.Match(ShGE03.Range("A5"), ShGE03.Range("T5:T1000"), 0))

I’m trying to update this program so those ranges reference a variable (RowData) that is

Dim ws As Worksheet
        Set ws = ShGE03  
Dim RowDataStart As Long

RowDataStart = 5

Dim RowDataEnd As Long

RowDataEnd = Cells(Rows.count, 5).End(xlUp).Row

Dim RowData As Long

For RowData = RowDataStart To RowDataEnd

My last attempt, that resulted in an error, was

R = WorksheetFunction.Index(ws.Range(ws.Cells(RowData, 5)), WorksheetFunction.Match(ws.Range(ws.Cells(1, 5)), ws.Range(ws.Cells(RowData, 20), 0)))

Any Help or suggestions

 

[rlv01]

    R = WorksheetFunction.Index(ShGE03.Range("E5:E1000"), WorksheetFunction.Match(ShGE03.Range("A5"), ShGE03.Range("T5:T1000"), 0))

If you use range variables you can rewrite your original fixed range formula as

    Dim CellRange1 As Range, CellRange2 As Range, CellRange3 As Range

    Set CellRange1 = ShGE03.Range("E5:E1000")
    Set CellRange2 = ShGE03.Range("A5")
    Set CellRange3 = ShGE03.Range("T5:T1000")

    R = WorksheetFunction.Index(CellRange1, WorksheetFunction.Match(CellRange2, CellRange3, 0))

Then it's just a matter of redefining the range variables for a new range

    Dim CellRange1 As Range, CellRange2 As Range, CellRange3 As Range
    Dim ws As Worksheet
        Set ws = ShGE03
        
    With ws
        Set CellRange1 = .Range("E5", .Range("E" & .Rows.Count).End(xlUp))    'range to last cell in column E w/data
        Set CellRange2 = .Range("E1")
        Set CellRange3 = .Range("T5", .Range("T" & .Rows.Count).End(xlUp))    'range to last cell in column T w/data
    End With

    R = WorksheetFunction.Index(CellRange1, WorksheetFunction.Match(CellRange2, CellRange3, 0))

(not tested).

 

[KDS14589]

    R = WorksheetFunction.Index(ShGE03.Range("E5:E1000"), WorksheetFunction.Match(ShGE03.Range("A5"), ShGE03.Range("T5:T1000"), 0))

If you use range variables you can rewrite your original fixed range formula as

    Dim CellRange1 As Range, CellRange2 As Range, CellRange3 As Range

    Set CellRange1 = ShGE03.Range("E5:E1000")
    Set CellRange2 = ShGE03.Range("A5")
    Set CellRange3 = ShGE03.Range("T5:T1000")

    R = WorksheetFunction.Index(CellRange1, WorksheetFunction.Match(CellRange2, CellRange3, 0))

Then it's just a matter of redefining the range variables for a new range

    Dim CellRange1 As Range, CellRange2 As Range, CellRange3 As Range
    Dim ws As Worksheet
        Set ws = ShGE03
       
    With ws
        Set CellRange1 = .Range("E5", .Range("E" & .Rows.Count).End(xlUp))    'range to last cell in column E w/data
        Set CellRange2 = .Range("E1")
        Set CellRange3 = .Range("T5", .Range("T" & .Rows.Count).End(xlUp))    'range to last cell in column T w/data
    End With

    R = WorksheetFunction.Index(CellRange1, WorksheetFunction.Match(CellRange2, CellRange3, 0))

(not tested).

Works GREAT
THANKS

 

[rlv01]

Glad I could help.