Do not calc. if next row is empty ? 2

[Recce]

Hi, I'm sorry but I'm new to VBA for Excel and do not seem to figure out how to stop a calulation when the next row have no information. I would like to read column A (which is no. format) and when the next row (never know which one it will be) is empty the the calculation should end.

Thanks :->

 

[Loomah]

Hi
I think you'll have to supply a little more info about what you're trying to do!

What is the calculation (is it on the worksheet or are you reading each cell in column a and eg adding them together programatically)?
How are you reading the values?

You can find the last row (sort of) containing data in column A by using either
range("a1&quot.end(xldown).row
or
range("a65536&quot.end(xlup).row

In both of these cases the next row down is empty (unless you only have 1 row of data!)

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Recce]

Hi Loomah

Yes, I understand and will try and supply more info. Attached is the code of a macro that I call. This code currently put the IF statement in all the cells of Range(E2:E65535) but, this is stupid codeing and precessing intensive. I would like for this calc to only happen if there is a number value in column A. Yes, I wil see to it that the column (column A) is sorted. Thanks for your help :->



If Sheet1.Range("D65535&quot = vbNullString Then

Sheet1.Range(&quot;E65535&quot.End(xlUp).Offset(1, 0) = &quot;=IF(OR(RC[-4]<>&quot;&quot;&quot;&quot,IF(OR(RC[-3]<>&quot;&quot;&quot;&quot,IF(OR(RC[-2]<>&quot;&quot;&quot;&quot,IF(OR(RC[-1]<>&quot;&quot;&quot;&quot,RC[-2]+RC[-1],&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot&quot;
Sheet1.Range(&quot;A1&quot.Select
End If

 

[Loomah]

Still not entirely sure this is what you want but...
This will put your formula into column E on the last row containing data in column A

Dim lRow As Long
With Sheet1
    lRow = .Range("A65536").End(xlUp).Row
    .Cells(lRow, 5).FormulaR1C1 = &quot;=IF(OR(RC[-4]<>&quot;&quot;&quot;&quot;),IF(OR(RC[-3]<>&quot;&quot;&quot;&quot;),IF(OR(RC[-2]<>&quot;&quot;&quot;&quot;),IF(OR(RC[-1]<>&quot;&quot;&quot;&quot;),RC[-2]+RC[-1],&quot;&quot;Check Values&quot;&quot;),&quot;&quot;Check Values&quot;&quot;),&quot;&quot;Check Values&quot;&quot;),&quot;&quot;Check Values&quot;&quot;)&quot;
    .Range(&quot;A1&quot;).Select
End With

Just as a side issue I was a little confused with your formula. Depending on what result you want I think you could get away without using all the 'OR' operators as they're not comparing anything - but that might just be me!

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Hmmm - formula could certainly be shorter but only by reducing the IFs
Seems to be checking to see if any of the 4 cells to the left are NOT blank

This would do the same:
&quot;=IF(OR(RC[-4]<>&quot;&quot;&quot;&quot;,RC[-3]<>&quot;&quot;&quot;&quot;,RC[-2]<>&quot;&quot;&quot;&quot;,RC[-1]<>&quot;&quot;&quot;&quot,RC[-2]+RC[-1],&quot;&quot;Check Values&quot;&quot

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Loomah]

xlbo
the formula can be shortened by removing the ORs (oars?!) or the IFs
The result is different, so shortening depends on desired result
Remove the ORs and calc is only performed if ALL cells are not &quot;&quot;, which is the same effect as the existing formula.
ie
&quot;=IF(RC[-4]<>&quot;&quot;&quot;&quot;,IF(RC[-3]<>&quot;&quot;&quot;&quot;,IF(RC[-2]<>&quot;&quot;&quot;&quot;,IF(RC[-1]<>&quot;&quot;&quot;&quot;,RC[-2]+RC[-1],&quot;&quot;Check Values&quot;&quot)))&quot;

Remove the IFs and calc is performed if ANY cell is not &quot;&quot;
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Loomah]

That should, of course, have looked like this!!!
&quot;=IF(RC[-4]<>&quot;&quot;&quot;&quot;,IF(RC[-3]<>&quot;&quot;&quot;&quot;,IF(RC[-2]<>&quot;&quot;&quot;&quot;,IF(RC[-1]<>&quot;&quot;&quot;&quot;,RC[-2]+RC[-1],&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot&quot;

D'OH
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Aaaaah - in that case, you can shorten even further by using AND:

&quot;=IF(RC[-4]<>&quot;&quot;&quot;&quot;,IF(RC[-3]<>&quot;&quot;&quot;&quot;,IF(RC[-2]<>&quot;&quot;&quot;&quot;,IF(RC[-1]<>&quot;&quot;&quot;&quot;,RC[-2]+RC[-1],&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot,&quot;&quot;Check Values&quot;&quot&quot;

becomes:

&quot;=IF(AND(RC[-4]=&quot;&quot;&quot;&quot;,RC[-3]=&quot;&quot;&quot;&quot;,RC[-2]=&quot;&quot;&quot;&quot;,RC[-1]=&quot;&quot;&quot;&quot,&quot;&quot;Check Values&quot;,RC[-2]+RC[-1])&quot;

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Recce]

Thanks for all the advice guys, also for the IF statements I changed that as well in order to make it shorter.

Loomah, you have the right idea and it puts the calculation right at the last row however, I also need to fill all the cells above that from E2 untill where the calculation stops at the last row. Hope this is clear. :->

 

[xlbo]

change:
.Cells(lRow, 5).FormulaR1C1
to
.range(Cells(2, 5),cells(lRow, 5)).FormulaR1C1

should do the trick

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Loomah]

My learned and inspirational colleague is, of course, correct. It should and, indeed, it will!!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Recce]

Excellent, this works perfectly. Thanks xlbo and loomah and the other guys for he help.

 

[xlbo]

Recce - if Loomah has helped (which he has), you should really give him a star. This is also useful for those people that use the archives as it points them to similar questions where a good answer / resolution has been given. You can do this by clicking on the &quot;Mark this post as a helpful / expert post&quot; link at the bottom of the appropriate thread.

PS - please don't give me a star - Loomah did the bulk of the work here and I'm sure that he would've responded with exactly the same change as I did for filling the entire range (if he had a faster connection ;-) )

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Recce]

Guys, you have both been awarded the pink star for bravery while operating under dangerous codeing circumstances with very inexperienced fellow coders like me....he..he

Cheers