Help on error using IF function

[brdstyle]

Hi guys

I write complicated code cuz am kinda low experince in Excel . ans it seems i need help.
To know where I need help I should first explain how do I use the file

We have some customers machines list
2 Columns is important
SMR = machine hour meter (D)
Last job = last job opend in the system (E)

when the machine reach 800 or above we open a job for 2000
when the machine reach 1800 or above we open a job for 3000 ... (2800 goes to 4000 , 3800 goes to 5000 ... etc)

other columes from (F) to (J) will look into (E) and do the following:
- if the last job is equal to them it will show (Done).
- if the last job is lower then them but the SMR is beigger it will show (YES)
- if the last job is lower then them and the SMR lower it will not show anything

as an example i use this formula for the colume (2000)

Please Login or Register  to view this content.

the problem is :
some of the results show false and i can not fix it nether to know where is the mistake

I have attached the file for more info and easier understanding.
SMR UPDATE.xlsx

I know its a chalange but I also know that u guys can do it.
Thanks in advance

[rishikrsaw]

Hi You are not using any condition on last if condition that why false result is occurring pls use as follow

for 2000 =IF(E3=0," ",IF(E3>=2000,"DONE",IF(D3>E3,IF(D3>800,"YES"," "),"")))

3000 = =IF(E2=0," ",IF(E2>=3000,"DONE",IF(D2>E2,IF(D2>1800,"YES"," "),"")))

4000= =IF(E2=0," ",IF(E2>=4000,"DONE",IF(D2>E2,IF(D2>2800,"YES"," "),"")))


and in the same way for 5000 and 6000

many thanks
Rishi

[brdstyle]

Hi Rishi

hmm interesting .. thx alot really :D

I think i have got it in another way also
for 2000

Please Login or Register  to view this content.

this will be shorter

now can use it in VBA insted of paste this in all the columns ???
I have more than 980 rows this will kill me lool

any idea???

[rishikrsaw]

sure you can just give some time ill be sharing with you macro...


Click on the star if you think I helped you

[rishikrsaw]

Please Login or Register  to view this content.

[Fotis1991]

rishikrsaw

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[rishikrsaw]

Sorry fotis,

now I put my code in code tag..

sorry for inconvenience cause

[rishikrsaw]

pls take of the range..

many thanks

[Fotis1991]

rishikrsaw

Ok. This is my last warning. If you continue to ignore Forum Rules amd Moderators requests, you'll be a Banned user.

[brdstyle]

Rishi

great work man i tryied it and it work just fine
but it seems there is a problem
when ever I delet any cell or row the code stop working i tried many ways but not working at all
even i can not start it in new workbook

can i have some aid pls

[arlu1201]

What error do you get brdstyle?

[rishikrsaw]

Hey brdstyle,

I have made few changes on code after excluding the select and activate

I have new enhanced version of code where our last code requered some manual task it is fully automated.


pls use it and let me know if you have still any problem

Please Login or Register  to view this content.

[rishikrsaw]

we can also create udf (user define function for it )

[brdstyle]

What error do you get brdstyle?

Hi

After finding the right way on how is the formula works am trying to get it in VBA insted of pasting it in all columns
the formual is

Please Login or Register  to view this content.

this is for the first colume

Rishi here is helping me alot

but i dont know why when i use his code it never work
even if i am using new workbook and save it as enabled macro file
still not working automaticly

[brdstyle]

Hey brdstyle,

I have made few changes on code after excluding the select and activate

I have new enhanced version of code where our last code requered some manual task it is fully automated.


pls use it and let me know if you have still any problem

Please Login or Register  to view this content.

Rishi

(

still the same even in new workbook and also change the PC
still not working automatic i dont know whats the problem really

[rishikrsaw]

Hey can u pls check ur formula "=IF(E2=0," ",IF(E2>=2000,"DONE",IF(C2>800,"YES","")))"
as value in c2 = "ALYAMAMA".....

I think correct formula is =IF(E2=0," ",IF(E2>=2000,"DONE",IF(d2>800,"YES","")))"

and I will be sending you the sheet in which i run the macro......

[brdstyle]

Hi Rishi

sorry for all the trouble

this is the correct formula for the 2000 column

Please Login or Register  to view this content.

i hope we get it at the end

[arlu1201]

You are saying you want it to work automatically? Can you elaborate on that?

[rishikrsaw]

sure I will be sharing my sheet where I was working on....

[rishikrsaw]

@arlu1201 I meant for automatic, where no need to feed data range as per the sheet as in if in column E there are 150 rows for "last job" so If using first vba code we need to put 150 else it will pick up range 1500

my new vba code doesn't requited to put 150 or any data range. it will automatically pick up range where is the column e laying...

I think, I have elaborated

[rishikrsaw]

pls use this sheet and do let me know incase of query

[arlu1201]

@arlu1201 I meant for automatic, where no need to feed data range as per the sheet as in if in column E there are 150 rows for "last job" so If using first vba code we need to put 150 else it will pick up range 1500

my new vba code doesn't requited to put 150 or any data range. it will automatically pick up range where is the column e laying...

I think, I have elaborated

Rishikrsaw,

I am asking brdstyle about it. He needs to elaborate as per the requirement.