Use relative reference in a formula to replace cell contents?

[Greenstripe]

I want to replace values (1-9) in the adjacent column on the left with another value. This formula is not working:

=IF(AND(E1>=1,E1<=10),RC[-1]"*","")

I think it's the RC portion of the formula that is expressed incorrectly but I am not sure.

 

[Joe4]

Welcome to the Board!

Are you trying to do this in a formula, or in VBA?
Note that Excel formulas CANNOT update values in other cells, they can only return values in the cell that they reside in.

Also, note that you formula is looking for numbers between 1 and 10, not 1 and 9, since you used: E1<=10

Lastly, what are you trying to replace the value with? A blank or something else?
For what purpose?
If just for display purposes, you could simply "hide" value using Conditional Formatting (by making the font color match the background color).

 

[Greenstripe]

Thank you for the welcoming words.

I began by trying this in VBA but after much fruitless searching/trying thought I'd try it with a formula instead.

I am trying to "suppress" certain cells in a data set, precluding them for inclusion in some statistical analysis. In the past, the team replaced single-digit data with an asterisk, doing so manually and according to a specific pattern. I was asked if it could be automated somehow. I'm confident that it can but have also realized the extent of how little I know VBA and/or Excel.

So, in this case (thanks for catching my typo), I am looking to replace values 1-9 with an asterisk. I can use an AND statement to return an asterisk adjacent to the cells in question, but I prefer to replace the value instead.

The second part of my task is to do the same operation as intended on another adjacent cell above it to further mask the data, and to do so every X rows. For this step I imagined that a similar formula, running down the next column over, would accomplish the same task using variations of, e.g., RC[-1].

Do this makes sense? In other words, I'm running a search & replace on single-digit characters, replacing the value with an asterisk. Then replacing data in an adjacent cell with an asterisk. Given the predictability of the pattern, I feel a programmatic solution is possible. But I am not sufficiently familiar with Excel to come up with a solution.

 

[Joe4]

OK. Here is some VBA code that I believe will do what you want. I added some comments to explain what certain steps are doing.

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column E with data
    lr = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through all rows starting in row 1
    For r = 1 To lr
'       Check to see if value in column E is between 1 and 9
        If Cells(r, "E") >= 1 And Cells(r, "E") <= 9 Then
'           Change value to asterisk
            Cells(r, "E") = "*"
'           Also change value in column D to asterisk
            Cells(r, "D") = "*"
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Greenstripe]

OMG this works beautifully! Thanks for spelling it out, much appreciated. The steps I need to do are a little more complicated but this points me in the right direction to try it on my own. I originally tried starting from the bottom because of the chronology of the data set. However, your script shows that it is not necessary to do so.

The arrangement of data I have is based on clusters of product type in a chronological sequence.

Rather than cells in column D (adjacent to the left of E), I need to suppress cells that are above it, one at a time in 25-cell increments. Here's how I imagine the transposition might be written:

Cells(r,"E-1(R[-25]C)") = "*"

In other words, from the initial asterisk go upwards (still column E) 1 cell and replace with an asterisk. Then go up 25 cells in the same column and replace value with an asterisk. Of course, I am not writing this correctly but this is what I am wrestling with.

 

[Joe4]

The format of Cells is Cells(row, column)
So, if you want to move up 25 rows from the cell just identified in my code, just use:

Cells(r-25,"E")