Change values in unknown range

[KLund1]

I have a unknown range of a constant value. I do not know the start row, or how many rows of the constant value there are, but they are continuous in the column. I need to change the values 2 columns over from positive to negative that are on the same row as the constant value.
Anyone have some VBA code that can do this?
Thanks everyone. Lots of good help here !!!

 

[p45cal]

you know the value of the constant, and you know the column?

 

[KLund1]

Thanks for the reply.
Yes. The constant value(24650.0) in column D, column F is where the values I want to change the sign.

 

[p45cal]

something like this?:

Sub blah2()
Dim xxx As Range
Set xxx = Columns("D").Find(what:=24650, LookIn:=xlFormulas, lookat:=xlWhole)
If Not xxx Is Nothing Then
  Do
    xxx.Offset(, 2).Value = -xxx.Offset(, 2).Value
    Set xxx = xxx.Offset(1)
  Loop Until xxx <> 24650
End If
End Sub

 

[laneglobal]

You don't need a macro:

1, Create two sheets (sheet1 and sheet2). On "sheet2" is where your constants are located.

2. On "Sheet1" place the following equation into cell A1 "=IF(Sheet2!A1=0,"",-Sheet2!A1)"

3. Copy cell A1 on "Sheet1" highlight all potential cells the constant could potentially go to there has to be a max. Maybe the max of excel is what you want; however there has to be a max. Example: A1:KJ75.

4. Once the max range is selected paste special formulas.

Done your numbers are all converted to negatives and zero's are blank if you want it to show zero change "" to 0.

 

[KLund1]

p45cal
PERFECT!!! just what i needed
Works GREAT!!!
thanks for this.