zookeepertx
Well-known Member
- Joined
- May 27, 2011
- Messages
- 589
- Office Version
- 365
- Platform
- Windows
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:
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:
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!
Here's the original code:
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 WithThe 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:
Code:
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 WithIf 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!
ffice
></o
