Excel VBA Replace Values Dynamically

[Matt29108]

Hello,

In the code below, I am selecting a specific range. Within that range I am replacing all the cells that contain $O$1 with a formula which contains the RowNumber. Note that everytime I run the code, the RowNumber value changes. So I cannot simply write a number as a replacement. When I run the code replace the $O$1 with the Offset formula, Excel do not see the RowNumber as an integer but as a word like the picture below. How can I make Excel recognize RowNumber as a number during replacement?

Thanks in advance for the help.

Sub Example()

Dim wb As Workbook
Dim ws As Worksheet
Dim RowNumber As Integer

Set wb = ActiveWorkbook
Set ws= Sheets("ws")

RowNumber = Application.WorksheetFunction.CountA(ws.Range("E:E"))

ws.Range(ws.Cells(RowNumber + 14, 1), DK.Cells(RowNumber + 47, 4)).EntireRow.Replace What:="=$O$1", Replacement:="Offset(RowNumber,0,1)"

End Sub

 

[mackc557]

For this section:

ws.Range(ws.Cells(RowNumber + 14, 1), DK.Cells(RowNumber + 47, 4)).EntireRow.Replace What:="=$O$1", Replacement:="Offset(RowNumber,0,1)"

simply change

Replacement:="Offset(RowNumber,0,1)"

to this

Replacement:="Offset(" & RowNumber & ",0,1)"