Macro to replace blank cell

[LLT]

Hi Expert,

I need to create macro to look up for column F, if column F is balnk, it will take value in column G but I run into error. I think "" I put is wrong. Can anyone help me on this?
My macro is as follow

Range("I18:I65536").Formula = "=IF(F18="",G18,-F18)"

 

[mancubus]

try

Range("I18:I65536").Formula = "=IF(F18="""",G18,-F18)"

 

[Peter_SSs]

Your syntax needs to be as below, but do you really need the formula to go down 65,000+ rows?

Range("I18:I65536").Formula = "=IF(F18="""",G18,-F18)"

 

[LLT]

Hi,

Thanks but how come the result is #VALUE!

 

[Peter_SSs]

Hi,

Thanks but how come the result is #VALUE!

My guess is that on that row, column F contains text, not a number.

 

[LLT]

Is there anyway to solve the #VALUE! as I have checked the column F is in number.

 

[Peter_SSs]

1. What row is it?

2. What is in columnf F of that row? If it is a formula, please give the formula as well as the result.

3. Suppose the row is row 28, put this formula in a vacant cell:
=ISNUMBER(F28)
Confirm that this formula returns TRUE

4. Suppose the row is row 28, confirm that the formula in I28 is:
=IF(F28="",G28,-F28)

 

[LLT]

Thanks, it works now when I use ISNUMBER, then show TRUE etc.

Another question, how to delete entire row if Column I =0?

 

[Peter_SSs]

Thanks, it works now when I use ISNUMBER, then show TRUE etc.

What do you mean? This was just a one-off formula in a vacant cell to confirm whether column F really did contain a number. It would have no effect on the rest of the formulas or code. If your column F formula was returning #VALUE! before, it would still be.

Another question, how to delete entire row if Column I =0?

1. Manually or vba?

2. Starting at a particular row (eg row 18)?

3. If it is to start at row 18, is there anything already in I17?

4. Is this needed because the formulas went all the way down to the bottom of the sheet from the earlier code? Are there other 0 values in amongst your data or just from the bottom of your data down to the bottom of the sheet?

 

[LLT]

Sorry to cause confusion.

For the previous error showing #Value. I solved it by setting a column I to show True or False result using formula =ISNUMBER(F2). Then set column J to show result =IF(I2=FALSE,G2,-I2).

Now how to create macro to delete column J if this is equal to 0?