Changing the value of some cells to negative numbers

[zookeepertx]

Hi there,
I just LOVE this board; it's so educational!

I've got a macro that works for a particular spreadsheet, but part of it runs a bit slowly & I don't know why. A lot of the macro is just formatting & moving columns around, but here's what the slow part does: Column H contains a number - always either 18 or 35. Column I contains a dollar value, but the report shows all the values as positive numbers. Any rows with 18 in Col H need to be a negative value in Col I. This report is a different length every time it's run.

I've got other, way more complicated macros on sheets of 1000 or more rows that will finish in just a second or two, but just this portion of this macro takes about 30 seconds on a report of about 500 rows. I'm sure there's a way to speed it up, but I don't know what it is. (Personally, I'm REALLY proud of having just figured out how to get it to do what I want at ALL, LOL!) Can anybody tell me how to get this to run faster?

Here's my code for that part of the macro:

    nRow = 2
    Do While Not IsEmpty(Cells(nRow, 1).Value)
        Range("I:I").Select
        Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
            If ActiveSheet.Cells(nRow, 8).Value = 18 Then
                ActiveSheet.Cells(nRow, 9).Value = (Cells(nRow, 9) * -1)
            End If
        nRow = nRow + 1
    Loop

I really appreciate any help!

 

[jbeaucaire]

Looping cell by cell and physically interacting with the worksheet within each loop is extremely inefficient.

I would search UP column H for the last used cell, then I would apply the formatting and formulas to column I all at once:

Option Explicit

Dim LR As Long

LR = Range("H" & Rows.Count).End(xlUp).Row

With Range("AA2:AA" & LR)
    .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .FormulaR1C1 = "=IF(RC8=18, -RC8, RC8)"
    .Value = .Value
    .Cut Range("I2")
End With

That should be near on instantaneous. This presumed column AA was empty and could be used to create a new set of numbers all at once.

 

[zookeepertx]

Perfect with just one little tweak.
Just needed to change this:

.FormulaR1C1 = "=IF(RC8=18, -RC8, RC8)"

to this:

 .FormulaR1C1 = "=IF(RC8=18, -RC9, RC9)"

And now it just runs faster than I can blink, LOL!

Thank you!! This is GREAT!

 

[jbeaucaire]

When you find yourself doing the "same" thing to an entire column of information, remember this technique. Worksheet functions can be fast, so if you can construct a formula to do this for you in an empty column, then you can do that all in one go, then paste the results back into your table, again all in one go.

Don't forget to turn off ScreenUpdating before you do stuff like this, then back on when you're done, makes what little lag that might occur with formulas drop to the barest minimum.

 

[zookeepertx]

Thanks for the suggestion! I'll remember next time. What I've done in the past was to sort the sheet by Col H, copy/paste the code 18 values into another row, giving them a negative value, then paste them back where they belonged. That was just annoying, though, LOL!

Just for my own knowledge (I'm still learning), I get this part:

With Range("AA2:AA" & LR)
    .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .FormulaR1C1 = "=IF(RC8=18, -RC8, RC8)"

But not so much this:

.Value = .Value
    .Cut Range("I2")
     End With

What does the .Value = .Value do? And how does the data get pasted back into Col I without using "Paste" in the code? (dumb question, I know, but I gotta ask, heheh)

Thanks again!

 

[jbeaucaire]

You see we use the "with (range)" method to apply a series of commands to the same range of cells...

First command applies the numberformat you wanted to that range.
Next inserts a formula into that range using the R1C1 syntax so each cell adjusts itself
The .Value = .Value removes the formulas but leave the "values" currently showing in the cells behind.

We actually are doing a cut/paste, but we're doing it in one line. The .Cut actually removes the values from the range and pastes them into the cell noted, Range("I2"). That's the target, it pastes down from there.

 

[zookeepertx]

Awesome! Thanks for the explanation! I've just always thought you had to use "paste special-values" to remove the formula. I'll bet I can use this to clean up some of my other macros.

I've seen R1C1 sytax referred to before, but I don't really "get" it. Can you explain to me what that means?

 

[jbeaucaire]

R1C1 translates to Row1Column1, or $A$1. Using the R1C1 you can create formulas with row can column references.

In your sample formula "=IF(RC8=18, -RC9, RC9)" then row numbers are missing, which makes the formula dynamic for rows, it will adjust to "this row" for each cell it is placed in. But the "C8" means column 8 which is column H and since it's specified, it's absolute.

So your formula reads "If the cell in column H on this row = 18, show me the negative of the column I value from this row, else show me the regular value."

You should read up in Excel's help file (Press F1) on uses of the R1C1 style.

 

[zookeepertx]

Great! You explain things very well!! I have tried reading the help file before & I just can NOT make sense of it. I'm a visual learner, not verbal, so technical instructions rarely do anything for me, other than frustrating me.

I've been working on adapting your code for a different report & have hit a small glitch. I started a new thread for it; maybe you can solve the problem. The thread is here: http://www.mrexcel.com/forum/showthread.php?t=574520

Many thanks for your help!

 

[jbeaucaire]

We should've just continued that discussion here, it's all the same issue.