Help With Mulitplying Range by Values in Another Column

[dgilrein]

I am using the following code to name a range in my worksheet and then dividing every cell in that range by the corresponding cell in column B and multiplying it by the corresponding cell in column A. Nothing happens when I run the code like it is, but if I substitute a range (like AU4:AT) for Rng, the macro works. But I need to name a dynamic range because the cells that I am changing are not always in the same place, but the first cell is always two rows below the first 'Pay' in the second row. Any help would be appreciated. Thanks!

Sub Test()
Dim Rng As Range
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

Rows("2:2").Select
Selection.Find(What:="PAY", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(2, 0).Select

Set Rng = Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight))

Range("Rng" & LR) = Evaluate("Rng" & LR & "/B4:B" & LR & "*A4:A" & LR)
End Sub

 

[Joe4]

Range("Rng" & LR)...

This part looks very wrong to me. Not sure what this is supposed to be doing.
You are defining a range variable named Rng, but "Rng" will return a literal string of "Rng".
If you intent was to return the range you just defined, concatenating a row number on to a range variable makes no sense.

So I am bit confused with what you are trying to do in that step.

 

[dgilrein]

I am trying to take the range that I just defined and then divide every cell in that range by the corresponding cell in column B then multiply that by the corresponding cell in column A.

 

[Joe4]

Is your range always contiguous?
Are you trying to use column A to find your last row, or your set Rng statement, i.e.

Set Rng = Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight))

The ActiveCell.End(xlDown) seems to be already looking for the last row, so is the LR calculation even necessary?

 

[Joe4]

Assuming that LR is redundant and unnecessary, here is how I would do what you want:

Sub Test()


    Dim myStartCell As Range
    Dim Rng As Range
    Dim cell As Range


'   Find where "PAY" first exists in row 2
    Rows("2:2").Select
    Selection.Find(What:="PAY", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
'   Set start cell to equal to two rows below where "PAY" is found
    Set myStartCell = ActiveCell.Offset(2, 0)
    
'   Select whole range below and to the right of the start cell
    Set Rng = Range(myStartCell, myStartCell.End(xlDown).End(xlToRight))
    
'   Loop through all cells in Rng and multiply by column A and divide by column
    For Each cell In Rng
        cell = cell * Cells(cell.Row, "A") / Cells(cell.Row, "B")
    Next cell


End Sub