Loop + "or"

Hello,

I am new to VBA and my approach is usually through trial and error and hope that it will work.

Unfortunately this time I am stuck and I hope you could help me.


I have created formula as below in cell H3:

'=IF((IF(OR(C3=0;B3=0;D3=0;E3=0;F3<=0);0;A4- A3))<0;0;IF(OR(C3=0;B3=0;D3=0;E3=0;F3<=0);0;A4-A3))

Then I have double clicked and the formula was copied down the column.
Calculations were performed within 10min and I got what I wanted.


I have read that using VBA to perform calculations will speed up calculations.
I have created macro as below which works on small number of cells but for the large number of rows it takes too much time.

I have estimated that the same calculations as above in my case with the loop will take 12h.

Can you please have a look and let me know what I am doing wrong?
I presume the problem is with the “or” function.

I am looking forward for any suggestions.

Flooyd

Please Login or Register  to view this content.

Hi,

you can try to disable some excel functions to speed up the process

Please Login or Register  to view this content.

hi,

yes, I have tried this, but not much better

hi,

I agree the formula is too complex, but works as I have been using it for a while and gives the results I am looking for.

I have got 7 columns:
A contains time stamp
B to F values

Column H should check value in cells B to F. As long as the logical test is true, than blank(“”). If the logical test is false it should subtract time form column A.
B to F logical test
B=0
C=0
D=0
E=0
F<0

I think this is covert by the VBA if

If Cells(i, 2) = 0 Or Cells(i, 3) = 0 Or Cells(i, 4) = 0 Or Cells(i, 5) = 0 Or Cells(i, 6) < 0 Then Cells(i, 8).Value = "" Else Cells(i, 8).Value = Cells(i + 1, 1).Value - Cells(i, 1).Value
On Error Resume Next

All the results are saved in column H.
Depends on quality of data I am receiving the subtract of time gives negative number, in order to avoid it I have created the VBA if, which looks for negative numbers and replace them with blank (“”)


If Cells(i, 8) < 0 Then Cells(i, 8).Value = ""

I think the loop approach is correct, please correct me if I am wrong.

Is it possible to replace the long expression:

If Cells(i, 2) = 0 Or Cells(i, 3) = 0 Or Cells(i, 4) = 0 Or Cells(i, 5) = 0
to something like this:

If Cells(i, j) = 0
If yes, would it help and reduce time?


Flooyd