# Variables in a range



## KDS14589 (Dec 18, 2022)

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 (Dec 18, 2022)

```
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 (Dec 19, 2022)

rlv01 said:


> ```
> 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
> ...


Works GREAT
THANKS


----------



## rlv01 (Dec 19, 2022)

Glad I could help.


----------

