If statement formula breaking VBA loop code

Question

I have a spreadsheet to track jobs that go through the workshop, on a separate sheet i have a macro that will only show live jobs. To determine which jobs to display, on the main sheet i have used an if statement formula like below:

=IF(NW3="complete","0","1")

so if a job is listed as complete it will show a 0

I have then written VBA code that will detect if each row in the correct column contains a 1, if so it will copy and paste that row into the live jobs sheet. however the VBA loop code does not recognise the number generated by the IF statement.

I know my code works because if i manually type a 1 or 0 in the columns instead of using the IF formula above, the VBA code finds all the results. I also made a random =SUM statement by dividing a cell by itself to give 1 and then ran the macro and again it worked. So it is only IF statement results it doesn't like.

Im stumped, please help!

Did you notice that =IF(NW3="complete","0","1") puts in a number as a string but =IF(NW3="complete",0,1) are real numbers. • If this doesn't solve your issue edit the question and add your code please. We cannot provide help on code that we don't see. — Pᴇʜ
– Pᴇʜ, Commented Feb 12, 2019 at 7:56

Harun24hr · Accepted Answer · 2019-02-12 07:57:06Z

2

=IF(NW3="complete","0","1") this formula giving you 1 and 00 as text not as number. So, change the formula to =IF(NW3="complete",0,1) which will give you output as number. Then VBA should work fine.

answered Feb 12, 2019 at 7:57

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

If statement formula breaking VBA loop code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related