PDA

View Full Version : VBA with a lot of condition statement



Dirz
07-31-2018, 04:45 AM
Good afternoon,

My name is Kevin. I have taken over a place where I have to manage data since few days.
I'd like to improve an excel file which don't really do the job expected.

This excel is using database to update some cell's value and there is a vba script to run. This script permit to update cell's value until it's correspond to certain request from customer.

It looks like that:



Sub check()
checkdone=0

Do until checkdone=1

point1=worksheets("sheet1").Range("O1")-worksheets("sheet1").Range("O2")
If point1<0 Then
point1=point1*-1
End If
If point1>3Then
point2=worksheets("sheet1").Range("O3")-worksheets("sheet1").Range("O4")
If point2<0 Then
point2=point2*-1
End If
If point2>5Then
point3=worksheets("sheet1").Range("O5")-worksheets("sheet1").Range("O6")
If point3<0 Then
point3=point3*-1
End If
If point3>2Then
point4=worksheets("sheet2").Range("O11")-worksheets("sheet2").Range("O12")
If point4<0 Then
point4=point4*-1
End If
If point4>4Then
...
...
...
checkdone=1

End if
End if
End if
End if

loop

End Sub

The 'pointxxx' can go up to more than 100.. So there is a lot of conditions, and the script takes a long long looong time to run until returns me an error. But when the code has not too much conditions, it's work...

Could you help me to improve these code please?


Thanks a lot!
Kevin

Hightree
08-03-2018, 09:27 AM
What is the meaning of these point how are these generated?

Dirz
08-06-2018, 06:51 AM
'Points' represent the difference between 2 values (One active and one reserve).
The aim is to be sure that the difference between the 2 values is more than X (X depends of requirements from customer).
Sometimes I have also to check if the values are below Y (Y depends of requirements from customer too)

snb
08-06-2018, 07:05 AM
Ik zond je een PM.