Formula: If then Change, If not Leave Alone

[Maverick99]

I would like to apply a formula to a 70,000 row column that will allow me to change a cell's value if a particular condition exists, and if it doesn't then leave the current value alone.

Example:

G5 Rev-Budget 100 If ((G5 = Rev-Actual),ABS(G5),Leave Value Alone)
G6 Rev-Actual -120 If ((G6 = Rev-Actual),ABS(G6),Leave Value Alone)
G7 Exp -50 If ((G7 = Rev-Actual),ABS(G7),Leave Value Alone)

G5 = 100
G6 = 120
G7 = -50

I'd like to do this within the column of data instead of creating a seperate column, writing a similar formula and then copying and pasting the values over the first column of data.

Thanks in Advance,

Maverick

 

[erik.van.geit]

Hi,

Obviously you can not have a formula AND a value in one cell.
So you could use code.

Do you want to apply this on an existing column once and for all, or do you need to do this dynamically, when an entry is made?

kind regards,
Erik

 

[Maverick99]

I would like to post this to an existing column "once and for all."

Thanks,

Maverick

 

[erik.van.geit]

this is the basic code you need
it assumes you select the data before running

Option Explicit
 
Sub test()
'Erik Van Geit
'code will BUG if some cells are containing errors like #NAME, #ISNA, etcetera, enable 'On Error'lines
 
Dim rng As Range
Dim arr As Variant
Dim i As Long
Dim j As Long
Dim RC As Long
Dim CC As Long
 
Set rng = Selection
arr = rng
RC = rng.Rows.Count
CC = rng.Columns.Count
 
    Application.StatusBar = "handling first column of data"
    'On Error Resume Next
    For j = 1 To CC
        For i = 1 To RC
        'do whatever with the data
        arr(i, j) = "add" & arr(i, j)
        Next i
    Application.StatusBar = Format(j / CC, "###%")
    Next j
    'On Error GoTo 0
    Application.StatusBar = False
 
rng = arr
Erase arr
End Sub

Now you will need to replace the line

arr(i, j) = "add" & arr(i, j)

by an if statement
can you do that?

I think the correct line would be

If Left(arr(i, j), 10) = "Rev-Actual" Then arr(i, j) = Abs(arr(i, j))

but I didn't test

 

[Maverick99]

Yes, I see what you mean, I didn't think think about using a Sheet Function. I'm going to start working on that part.

Thanks,

Maverick

 

[erik.van.geit]

and does this work for you?

 

[Maverick99]

I'm still working through parts of it, while taking care of other demands, and geeting ready for a meeting.

I wasn't to familiar with what you provided, but this is what I've put together so far. The Rev.Actual was actually a value (30050), and I used its description versus the value to better explain what I was looking for.

Sub test()
'Erik Van Geit
'code will BUG if some cells are containing errors like #NAME, #ISNA, etcetera, enable 'On Error'lines

Dim rng As Range
Dim arr As Variant
Dim i As Long
Dim j As Long
Dim RC As Long
Dim CC As Long
Dim FinalRow As Long

Set rng = Selection
arr = rng
RC = rng.Rows.Count
CC = rng.Columns.Count

Application.StatusBar = "handling first column of data"
'On Error Resume Next
For i = 1 To RC
If rng(i, 8) = 30050 Then
rng(i,11) = ABS(i,8)

Right now it keeps erroring out on the ABS statement.

Maverick

 

[erik.van.geit]

Hi,

please use the CODEtags to display code, just like I did

about your error: I showed how to do it

If Left(arr(i, j), 10) = "Rev-Actual" Then arr(i, j) = Abs(arr(i, j))

you forgot to use the red part
Abs(arr(i, j))

best regards,
Erik