Find & Replace All "x" values in a Column with the result of a spreadsheet function

[bs0d]

I have a spreadsheet with an Access query that returns data by date.
The query returns an "x" in a column next to each date where a match is found in another table in the database.

In Excel, I'd like to replace the x's with the result of this function:

=percentile.inc("D:D", 0.90)

I figure this would be much more difficult to do with the query, and easier to handle in Excel.

Any thoughts?

I'm not certain if you can insert a spreadsheet function in the Replacement: area of a .Replace statement. See below:

    Sheets("Sheet1").Range("R:R").Replace What:="x", Replacement:=percentile.inc("D:D", 0.90), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

 

[Leith Ross]

Hello bs0d,

Try this and see it works.

    Dim Func    As String
    Dim Rng     As Range
    
        Set Rng = Sheets("Sheet1").Range("R1:R" & Rows.Count)
        
        Func = "=Percentile.inc(""D:D"", 0.9)"
        
        Rng.Replace "x", Func, xlWhole, xlByRows, False, False, False, False

 

[bs0d]

Hello bs0d,

Try this and see it works.

    Dim Func    As String
    Dim Rng     As Range
    
        Set Rng = Sheets("Sheet1").Range("R1:R" & Rows.Count)
        
        Func = "=Percentile.inc(""D:D"", 0.9)"
        
        Rng.Replace "x", Func, xlWhole, xlByRows, False, False, False, False

Great, thanks I will try this and see if it works. One more thought, is there a way to replace all of the values that exist? Instead of assuming "x" ? Replace any value in the column with the function?


Thanks again

 

[bs0d]

It works, but it doesn't work... It replaces each "x" with the function, however the function inclues " and ", and results in #VALUE! ... If I try to replace the quotes, then it doesn't replace the X's with the function.

I'm using:

Func = "=Percentile.inc(""J2:J " & FinalRow & """, 0.9)"

It put's this in each cell: =Percentile.inc("J2:J500", 0.9) ... I need the quotes stripped out. Except when I tried it doesn't perform the replace any more (see below). Maybe I'm doing it wrong though:

 Func = "=Percentile.inc(J2:J " & FinalRow & ", 0.9)"

 

[bs0d]

Got it to work completely... I changed this:

 Func = "=Percentile.inc(J2:J " & FinalRow & ", 0.9)"

to this:

 Func = "=Percentile.inc(J2:J" & FinalRow & ", 0.9)"

Note the space after the J in J2:J " was tripping things up! Thanks for all the help