Avoid circular reference

[Markkens]

I have a series of data points in row 55 of worksheet 'data' I want to reformat:

IF P55 <1 THEN let P55 = '<1' (text)
IF S55 <10 THEN let S55 = '<10' (text)

...and so on.

Can this be done and how would it look in code?

Thanks!

 

[mikerickson]

Formulas in row 56 and hide row 55 is one option.

Why does column P have a different cut off than column S? and what is the cut off for the other columns?

 

[Markkens]

The cutoffs and replacement values are all different, I can modify if they're obvious.

The actual range (all on row 3) is: P,S,V,Y,AB,AE,AH,AK,AN,AQ,AT,AW,AZ,BC,BF,BI,BL,BO,BR,BU,BX

Unfortunately 'data' is populated from 2 down, and it's often regenerated with varying quantities of rows, but the sheet is totally clear from CB2 to the far extents.

I can easily add a scratch worksheet for formulas if that helps.

 

[lenze]

Maybe something like this!!

Sub Convert()
If [P55] < 1 Then [P55] = "<1"
If [S55] < 10 Then [S55] = "<10"
End Sub

lenze

 

[Markkens]

I had the logic, but not the syntax. Those little []'s were what I needed.

Circular reference is irrelevant since the macro's only firing once


THANKS!!!