Related:

- Excel Macro - using integers as cell referenc
- Excel macro insert image into cell - How-To - Excel
- Excel macro delete row if cell contains value ✓ - Forum - Excel
- Excel macro to merge cells based on condition ✓ - Forum - Excel
- Excel macro to create new workbook based on value in cells ✓ - Forum - Excel
- Excel macro insert picture based on cell value - Forum - Excel

## 2 replies

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

Andy

Few issues with you code

1. cell a, x is not correct

It needs to be

Cells(row, column)

like cells(3, "E") or cells(3,5)

The blocks of IF and FOR etc cannot be intersected.

Few issues with you code

1. cell a, x is not correct

It needs to be

Cells(row, column)

like cells(3, "E") or cells(3,5)

The blocks of IF and FOR etc cannot be intersected.

Andy May

In addition to the above, here is the formula that I put in the spreadsheet as a temporary solution to the problem:

=IF($E29="Other Non-Billable Expenses",0,IF($E29="NON-BILLABLE DIRECT COSTS",M24+M25+M26+M27+M28, IF($E29="CONTRIBUTION",M19-M27,IF($E29="Contribution %",(M28/M12)*100,IF($E29="Variance",M27-M28,IF($E29="","",J29))))))

=IF($E29="Other Non-Billable Expenses",0,IF($E29="NON-BILLABLE DIRECT COSTS",M24+M25+M26+M27+M28, IF($E29="CONTRIBUTION",M19-M27,IF($E29="Contribution %",(M28/M12)*100,IF($E29="Variance",M27-M28,IF($E29="","",J29))))))

For x ..

..

Next x

is a loop you dont have to say that if x=totalrow. it wll do that for you.

your if and else are causing issue and are not need

neither x=1 is needed

next x

next y

is all you need. For each value of y, it will executed the inner for loop.

Could you post a sample file at some share site like https://authentification.site and post link here.

https://authentification.site/files/21587279/Example_for_Leanne_-_macro_test2.xls

I realized that with the loop as well and modified it. I also changed a few things with the coding (I started x aat row 28 so the IF statements weren't necessary). I can run this without the FOR / NEXT loops and it will work (but only for one cell at a time...). Everytime I put the loops back in I get various FOR / NEXT related errors.

Thank you for your help. The file that's linked here also contains the data that should appear if the macro is working correctly.

I am on MS Excel 07, but I'm saving this as an xls book.

The error with the code is that the last lines should have been like this

End Select

Next x

Next y

For items that you dont have it here in case statements and have 0 in the sample sheet you can add one more case like this

Case "Other Revenue", "Gross REVENUE", "Cost of Sales", "Out of Pocket Expenses", "NET REVENUE", "Direct Labor Cost (Standard Costing)", "Contractors ", "BILLABLE DIRECT COSTS", "Gross Margin", "Gross Margin %", "New Business Direct Labor Cost", "New Business Expenses", "Non-Billable Direct Labor Cost", "Bad Debt Writeoff", "Target Contribution"

Cells(x, y) = 0

One more thing, CASE ELSE is the always the last case. It is default, if all cases fail then this will be true. You can entirely remove the case else also in which case there will be no default condition. So if you do add this new case in the select, make sure it appears before CASE ELSE

Thank you for your help and prompt follow ups!