CODEWITHSUNDEEP
excelvbaif-statementexcel-formula
BilboBaggins90
BilboBaggins90

Reputation: 21

If statement formula breaking VBA loop code

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!

Upvotes: 1

Views: 114

Answers (1)

Harun24hr
Harun24hr

Reputation: 36870

=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.

Upvotes: 2

Related Questions