IF statement in my macro- what am I missing?

[zookeepertx]

I have this great bit of code that works perfectly on the original spreadsheet. I'm trying to adapt it to work on a different sheet, but it seems to have a quirk that I can't figure out. It's part of a larger macro. If a number in Col H is 18, the value in Col I needs to be a negative, but if H is 35, I needs to stay positive.

Here's the original code:

    LR2 = Range("H" & Rows.count).End(xlUp).Row
    With Range("AA2:AA" & LR2)
        .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        .FormulaR1C1 = "=IF(RC8=18, -RC9, RC9)"
        .Value = .Value
        .Cut Range("I2")
    End With

The report I'm trying to adapt to has the 18 or 35 in Col B & the value in Col H. However, on this report, Col H has the value without using decimals. For example, 27.54 shows as 2754. So, I need to adapt the code so that an 18 in Col B causes H to be negative AND divided by 100 and a 35 in Col B causes H to just be divided by 100. I've accomplished that, but there are some blank rows that need to stay blank. So, here's what I've tried:

    LR2 = Range("B" & Rows.count).End(xlUp).Row
    With Range("N18:N" & LR2)
        .NumberFormat = "#,##0.00_);[Red](#,##0.00)"
        .FormulaR1C1 = "=IF(RC2=18, -RC8/100, IF(RC2=35, RC8/100, ""))"
        .Value = .Value
        .Cut Range("H18")
    End With

My problem is in the case of neither 18 nor 35 in Col B. If I stop right after RC8/100 & have no comma, the cells show as FALSE. If I DO have a comma, the cells show 0. That's what I expect. But if I do it like above, with a comma, space and "", I get a Run-time 1004 error!

If I type just the formula in the spreadsheet, including the , "" it works fine; it only haas the problem when it's in the macro.

I'm sure this is just something simple that I'm missing & you guys will spot it right off. Thanks for looking!

 

[andiemac]

I believe your problem is in the Else section. In your formula you have 2 sets of quotes, but the macro code thinks that those quotes are part of your code. Try:
"=IF(RC2=18, -RC8/100, IF(RC2=35, RC8/100, " & Chr(34) & Chr(34) & "))"

The Chr(34), once pasted into Excel, will turn into quotes.

 

[zookeepertx]

PERFECT!! Thank you!

 

[zookeepertx]

Just offhand - the original values have a border around the cells; do you know if/how I can make that border stay when the "post formula" value is pasted back into H? The blank rows don't have any borders or I'd just select the column & apply borders.
Thanks!

 

[Snakehips]

Just beat me to it!
"=IF(RC2=18, -RC8/100, IF(RC2=35, RC8/100, """"))"

Should also work.

 

[Snakehips]

Re retaining border, try.....

.Value = .Value
.Copy Range("H18")
.ClearContents

 

[zookeepertx]

Hmmm, well that SEEMED like it should work, but still no borders. I've tried inserting variations of PasteValues in different spots, but am having no luck.
Other ideas?

 

[Snakehips]

Hi,
Sorry, but I assumed that the, 'Temporary Computational' (?), cells in column N would be bordered similar to your col H cells. If you can border the cells in N, either permanently or conditionally based on your col B values then my suggested code will be ok. If it is a question of aesthetics then col N can be hidden, you will not see a column of empty bordered cells and all will still function.
Let me know if it works.

Tony

 

[jbeaucaire]

TO maintain the existing formatting in column I, you will have to replace one command line with three.

        .Cut Range("I2")

...becomes:

        .Copy 
        Range("I2").PasteSpecial xlPasteValues
        .ClearContents

 

[zookeepertx]

Hi Tony, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
No, the borders on the original report are only around the actual data. Thanks for your suggestions, though.<o></o>
<o> </o>
Hi Jerry,<o></o>
YES! That works perfectly! Thank you again!