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)"

 

[Peter_SSs]

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).

That would mean this earlier statement was incorrect?

I have checked the column F is in number.

Anyway, now you have
I2: =ISNUMBER(F2) [this returns TRUE or FALSE]
J2: =IF(I2=FALSE,G2,-I2) [this returns either what is in G2 or -1]
This seems quite a different outcome to the formula you started out with which was to return either what was in column G or the negative of what was in column F. Are you sure that is the formula you have in J2 and not this?
=IF(I2=FALSE,G2,-F2)

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

If you want my help to do that you would need to address the other 3 questions I asked about it.

 

[LLT]

Hi
My macro is run this way whereby column I will show True or False. Then Column J will show value (either the value in column G or column F). This eleminate the earlier error of #VALUE! as those empty cells in column F now show "0" in column J

Sheets("Estmt").Range("I2:I65536").Formula = "=ISNUMBER(F2)"
Sheets("Estmt").Range("J2:J65536").Formula = "=IF(I2=FALSE,G2,-F2)"

Ans to your qns :
- The reason why I use to row 65536 because the number of rows vary.
- I am using VBA to run all these.

 

[Peter_SSs]

Let's try to make this all a bit more efficient. Might not get it exactly right the first time because I still don't have a full picture of what you have and what you are trying to achieve but we'll start somewhere.

1. At least now you have answered the question I asked way back in post #3 about why you were using so many rows. Although your rows may vary, we should be able to work out how many you have and not waste those resources writing the formula all the way down the sheet then later deleting most of those rows.

2. Following from the above, is it correct that we could just write the formula(s) down to where the data ends in column G?

3. It seems like you have introduced a new column just to do =ISNUMBER(F2) but your comment in your last post seems to be indicating that the problem was simply that column F is sometimes blank. Is that correct?

If so, your original formula should not have returned an error if column F was blank. So, can you please confirm whether column F contains a formula or not. If it does, please post the formula from F2.

(I will probably have a few more questions resulting from the answers to these.)

 

[LLT]

Hi,

Really appreciate your help in guiding me on building the macro.


2. Following from the above, is it correct that we could just write the formula(s) down to where the data ends in column G?

LT: Let me illustrate how the data is presented in the table below so that you have clearer picture. The transactions are extracted from bank statement whereby it has the transaction description and Debit column (column F) and Credit column (column G) as shown below

<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=338 border=0 x:str><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 124pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=165 height=17> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=82>Column F</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=91>Column G</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Detail Description</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Debit</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Credit</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 124pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=165 height=34 x:str=" FROM INTERNET BANKING "> FROM INTERNET BANKING </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>28.28</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 124pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=165 height=51> FROM ATTACHMATE PTE</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>31.65</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>


3. It seems like you have introduced a new column just to do =ISNUMBER(F2) but your comment in your last post seems to be indicating that the problem was simply that column F is sometimes blank. Is that correct?

If so, your original formula should not have returned an error if column F was blank. So, can you please confirm whether column F contains a formula or not. If it does, please post the formula from F2.

Cells in column F contain a mixture of number and blank. That's is why it's puzzle me when I used the original IF formula (=IF(F2= """",G2,-F2), it give me the result of "#VALUE!" for those blank cells.

Column F does not contain any formula, it just contains either numbers or blank.

 

[Dryver14]

did you really mean

=IF(F2="",G2,-F2)

 

[LLT]

Hi,

I have tried this formula u hv mentioned too but keep having error when run macro.

 

[Peter_SSs]

Hi,

I have tried this formula u hv mentioned too but keep having error when run macro.

With the formula suggested and assuming an error in row 28 ..

In a vacant cell, what does this formula return?
=LEN(F28)

In a vacant cell, what does this formula return?
=CODE(F28)

 

[Peter_SSs]

Ignore the questions in my previous post, unless the suggestion below does not work.

I assume the transactions are downloaded from a web site. In that circumstance, cells that appear blank are often in fact not blank. Instead they contain some non-visible character. I suspect the results of the formulas suggested in my previous post would confirm that and that would be why the formulas you have been trying have been returning errors when column F appears blank.

Following from my earlier point about the number of rows required. I cannot imagine that the transaction list could need anything like 65,000 rows.

So try this code. It has two points to note:

1. We find the last row of data (by searching upwards from the bottom of columns F:G until we find some data) and then just fill the formula in those rows. Then there should be no need to subsequently go deleting rows that contain 0.

2. By using SUM() in the formula, we should avoid any problems with non-visible text characters in the cells since SUM() ignores text values.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Fill_Formula()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'Find the last row in cols F,G with data</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("F:G")<br>        LastRow = .Find(what:="*", After:=.Cells(1, 1), _<br>            LookIn:=xlValues, SearchOrder:=xlByRows, _<br>            SearchDirection:=xlPrevious, SearchFormat:=False).Row<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Just fill those rows with a formula</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> LastRow > 1 <SPAN style="color:#00007F">Then</SPAN><br>        Range("I2:I" & LastRow).Formula = "=SUM(G2)-SUM(F2)"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>