Macro for Find and Replace does nothing???

[TooZippy]

I tried using this macro from what I found on the internet and it does nothing. I am looking to change the cell reference in a formula in nine columns. I set up a table for this macro to draw from such as this...

[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Find[/TD]
[TD="width: 64, bgcolor: transparent"]Replace[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$M
[/TD]
[TD="bgcolor: transparent"]$Q[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$N[/TD]
[TD="bgcolor: transparent"]$R[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$O[/TD]
[TD="bgcolor: transparent"]$S[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$P[/TD]
[TD="bgcolor: transparent"]$T[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$Q[/TD]
[TD="bgcolor: transparent"]$U[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$R[/TD]
[TD="bgcolor: transparent"]$V[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$S[/TD]
[TD="bgcolor: transparent"]$W[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$T[/TD]
[TD="bgcolor: transparent"]$X[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$U[/TD]
[TD="bgcolor: transparent"]$Y[/TD]
[/TR]
</tbody>[/TABLE]

I checked in the other worksheet and nothing was changed there and nothing was done in the worksheet that this table and the columns with the formulas is in. Here is the macro I am trying to use...

Sub Find_and_Replace()

Worksheets("Run Macros").Activate
    Dim ws As Worksheet
    Dim rngFind As Range
    Dim strFind As String
    Dim strReplace As String
    
    Set ws = Worksheets("Run Macros")
    strFind = "$M"
    
    Set rngFind = ws.Range("F2:N11").Find(strFind, LookAt:=xlWhole)
    If Not rngFind Is Nothing Then
        strReplace = rngFind.Offset(0, 1).Value
    End If
    
    'strReplace should now equal $M
    '(Based on your lookup table)
End Sub

I even named the table in the Name Manager.

Thank you,

Jared Z.

 

[TooZippy]

I think that you got it. The formulas are in the columns with the headers KKK through SSS in the example above. Within those formulas are cell references in the COUNTIF function that I would like to change with the macro. What I like to change is in the Find column from above to replace with what is in the Replace column from above. All of this is in the same spreadsheet.

Thank you,

Jared Z.

 

[Fluff]

In that case try Jim's code without any changes, as that should work.

 

[steve the fish]

Maybe the replace is trying to replace based on whole cell contents rather than part.

 

[jim may]

Can you step through (using the F8 key) my code with both windows (your worksheet and your code) visible?
After the first loop - check the cell F2 for what should be the now changed cell references. Jim

 

[TooZippy]

Jim,

I tried using your macro again but it did not do anything. I found another macro on the internet that partially works. It finds the values and deletes them but does not replace them with the right values.

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Sub Find_and_Replace()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Rng As Range[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim InputRng As Range, ReplaceRng As Range[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]xTitleId = "KutoolsforExcel"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set InputRng = Application.Selection[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set InputRng = Application.InputBox("Formulas to Fix ",xTitleId, InputRng.Address, Type:=8)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set ReplaceRng = Application.InputBox("Find and Replace Values:", xTitleId, Type:=8)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]For Each Rng In ReplaceRng.Columns(1).Cells[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][SIZE=3][COLOR=#000000]InputRng.Replacewhat:=Rng.Value, replacement:=Rng.Offset(0, 1).Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Next[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub

In the "Formulas to Fix" dialog box, I use the cell range from F2:N10 (where the formula's are from above). In the "Find and Replace Values" dialog box I use the values in the table with the header "Find" and "Replace" (also from above). This macro is not replacing in the formula's the values in the table column labeled "Replace" but is using random values from the table. Any suggestions please?

Thank you,

Jared Z.
[/COLOR][/SIZE][/FONT]