Loop - Select row, find value, replace value

[Knockoutpie]

How can I loop this?

this is currently selecting row 4. Finding the value in AO4, and replacing the value in AQ4.
How can i loop this until the end of the sheet? Based on data in column E

next would be select row 5, find value in AO5, replace with value in AQ5

Rows(4).Select
    Cells.Replace What:=range("AO4").Value, Replacement:=range("AQ4").Value, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=True, _
        FormulaVersion:=xlReplaceFormula2

 

[DanteAmor]

Please check if the following helps you.

Sub replacevalues()
  Dim i As Long
 
  For i = 4 To Range("AO" & Rows.Count).End(3).Row
    Range("E:E").Replace What:=Range("AO" & i).Value, Replacement:=Range("AQ" & i).Value, _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=True
  Next
End Sub

If you are having difficulties, then to better understand your problem, I would like to ask a few questions:
- How are your data on the sheet.
- You only want to replace the data in column E.
- The change is a partial value within the cell or the entire value of the cell.
- One last thing, why do you have the ReplaceFormat parameter set to True? It means it depends on some format in the cell?

I hope to hear from you soon.
Respectfully
Dante Amor

 

[Knockoutpie]

Please check if the following helps you.

Sub replacevalues()
  Dim i As Long
 
  For i = 4 To Range("AO" & Rows.Count).End(3).Row
    Range("E:E").Replace What:=Range("AO" & i).Value, Replacement:=Range("AQ" & i).Value, _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
      ReplaceFormat:=True
  Next
End Sub

If you are having difficulties, then to better understand your problem, I would like to ask a few questions:
- How are your data on the sheet.
- You only want to replace the data in column E.
- The change is a partial value within the cell or the entire value of the cell.
- One last thing, why do you have the ReplaceFormat parameter set to True? It means it depends on some format in the cell?

I hope to hear from you soon.
Respectfully
Dante Amor

Hi Dante, I'm using column E to determine when the loop should stop, column E will tell me how far down the data goes to the bottom of the worksheet.
I'm using AO4 as my find value, and AQ4 as my replacement value, while having row 4 selected. (this will be my starting row)

Essentially, this is a way to replace the row number that a formula is referring to (but for all the formula for the whole row)

 

[DanteAmor]

For me to better understand the problem, could you answer the questions raised in post #2.
Put here some examples of your sheet.
Your macro does not work with row 4, what your macro does is make the change to the entire sheet, that is why I am confused, it even changes the data in the same cell AO4.
Now I don't understand why you consider column E as the base to stop the loop. If column E has 15 rows, but column AO only has 8 rows, the result will not be as expected.

I hope to hear from you soon.
Respectfully
Dante Amor

 

[Knockoutpie]

For me to better understand the problem, could you answer the questions raised in post #2.
Put here some examples of your sheet.
Your macro does not work with row 4, what your macro does is make the change to the entire sheet, that is why I am confused, it even changes the data in the same cell AO4.
Now I don't understand why you consider column E as the base to stop the loop. If column E has 15 rows, but column AO only has 8 rows, the result will not be as expected.

I hope to hear from you soon.
Respectfully
Dante Amor

I understand.
- How are your data on the sheet.
A. I would like to have it filtered so it only works through visible rows and we can disregard E
- You only want to replace the data in column E.
A. No, E will determine the last row of the sheet
- The change is a partial value within the cell or the entire value of the cell.
A. Partial value, essentially every cell in row 4 has a formula like "=SUMIFS('Customer'!17:17,'Customer'!1:1,Forecast!AM1)" i'm taking the value of AO4, and replacing it with the value in AQ4 (this will replace the row numbers in the above formula for all cells in that row, if i have the row selected)
- One last thing, why do you have the ReplaceFormat parameter set to True? It means it depends on some format in the cell?
A. it can be false, this was just the recorded code.

Attached is an image of the workbook

 

[Knockoutpie]

@DanteAmor

Hi Dante, this is what I came up with, let me know your thoughts. it seems to work for what I need.

Sub SelectVisibleRowsAndLoop()

    ActiveSheet.range("$A$1").AutoFilter Field:=43, Criteria1:="<>" ' filter for non-empty rows in column AQ
    Dim lastRow As Long
    Dim currentRow As Long
   
    ' Select row 4
    Rows("4:4").Select
   
    ' Get the last non-empty cell in column AQ
    lastRow = Cells(Rows.Count, "AQ").End(xlUp).Row
   
    ' Loop through all the visible rows until the last non-empty cell in column AQ
    For currentRow = 5 To lastRow
        ' Check if the current row is visible
        If Rows(currentRow).Hidden = False Then
            ' Select the current visible row
            Rows(currentRow).Select
           
            Dim cell As range
For Each cell In range("AO4:AO" & lastRow)
    Cells.Replace What:=cell.Value, Replacement:=range("AQ" & cell.Row).Value '....
Next

        End If
    Next currentRow
   
End Sub

 

[DanteAmor]

It looks like your code comes from chatgpt, that doesn't help me what you want to do.
If it works for you, perfect.

But if it doesn't work for you and following what you put as answers and what you show in your image, your formulas go from column A to column M.

I'm going to follow your suggestion to use column E as a reference.

Sub replacevalues()
  Dim i As Long
 
  For i = 4 To Range("E" & Rows.Count).End(3).Row
    If Range("AO" & i).Value <> "" And Range("AQ" & i).Value <> "" Then
      Range("A" & i & ":AM" & i).Replace Range("AO" & i).Value, Range("AQ" & i).Value, xlPart
    End If
  Next
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

 

[Knockoutpie]

It looks like your code comes from chatgpt, that doesn't help me what you want to do.
If it works for you, perfect.

But if it doesn't work for you and following what you put as answers and what you show in your image, your formulas go from column A to column M.

I'm going to follow your suggestion to use column E as a reference.

Sub replacevalues()
  Dim i As Long
 
  For i = 4 To Range("E" & Rows.Count).End(3).Row
    If Range("AO" & i).Value <> "" And Range("AQ" & i).Value <> "" Then
      Range("A" & i & ":AM" & i).Replace Range("AO" & i).Value, Range("AQ" & i).Value, xlPart
    End If
  Next
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

you're the best, I always like seeing your replies.

 

[Knockoutpie]

It looks like your code comes from chatgpt, that doesn't help me what you want to do.
If it works for you, perfect.

But if it doesn't work for you and following what you put as answers and what you show in your image, your formulas go from column A to column M.

I'm going to follow your suggestion to use column E as a reference.

Sub replacevalues()
  Dim i As Long
 
  For i = 4 To Range("E" & Rows.Count).End(3).Row
    If Range("AO" & i).Value <> "" And Range("AQ" & i).Value <> "" Then
      Range("A" & i & ":AM" & i).Replace Range("AO" & i).Value, Range("AQ" & i).Value, xlPart
    End If
  Next
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor

Hi Dante, just following up in case someone comes across this, I modified it just a tiny bit..

"E" - to determine the last row on the sheet
"AN" where my find value is
"AP" where my replace value is
Range("G" & i & ":AN" & i - my formulas are in column G:AN

Again, thank you for your help!

Sub Picture2_Click()

  Dim i As Long
 
  For i = 4 To Range("E" & Rows.Count).End(3).Row
    If Range("AN" & i).Value <> "" And Range("AP" & i).Value <> "" Then
      Range("G" & i & ":AN" & i).Replace Range("AN" & i).Value, Range("AP" & i).Value, xlPart
    End If
  Next
End Sub