Excel  Using integers as cell reference
Issue
OK, I've written this and I'm having some trouble. I've put the comments in the code (preceded by ****) rather than giving you a summary here. I think it's easier to follow:
Sub Tester()
Dim a As Integer
Dim x As Integer
Dim y As Integer



 a=5  this is the equivalent of column "E"


a = 5



 Count active columns and then rows. The totals will pin the ends of the ranges


' Count the number of data columns in the table
totalcolumns = ActiveSheet.UsedRange.Columns.Count
' Count the number of data rows in the table
totalrows = ActiveSheet.UsedRange.Rows.Count



 y begins at column G, and x can begin anywhere. Y = Column number, X = Row number.


For y = 7 To totalcolumns
For x = 1 To totalrows



 Everything that I do here fails, so I've actually just written this as simply as I could. a=5, x=ActiveRow and y=ActiveColumn. If specific text is found in cells in column E (a,x) then the values in column G (and beyond) should be overwritted with what I'm directing:


If Cell a,x = "Other NonBillable Expenses" Then Cell y,x = 0 Else If Cell a,x = "NONBILLABLE DIRECT COSTS" Then Cell y,x = Cell y,x5 + Cell y,x4 + Cell y,x3 + Cell y,x2 + Cell y,x1 Else If Application.IsText(a, x) = "CONTRIBUTION" Then Cell y,x = Cell y,x10  Cell y,x2 Else If Cell a,x = "Contribution %" Then Cell y,x = (Cell y,x1 / Cell y,x17) * 100 Else If Cell a,x = "Variance" Then Cell y,x = Cell y,x2  Cell y,x1 Else If Cell a,x = "" Then Cell y,x = "" Else End If End If End If End If End If End If



 Once it has scanned the column E for the text I want and replaced the result in Column G with the value I want I need it to move to the next row (Next X), and once it has looped through to the last row (totalrows), I want it to start over at x=1 and go to the next y (Next Column, which would be H) unless y the last column (totalcolumns)


If x = totalrows Then Next y x = 1 Else If y = totalcolumns Then End Sub Else Next x End If End Sub



 Something is horribly wrong with the group of IF's. It definitely has something to do with trying to use the integer as a part of the cell reference, but I'm not exactly sure how that works. Usually I'm combing either a column or a row only. I'm in over my head on this one, and I'm hoping that someone may have a little help for me.


Solution
You had missed End select. I used the data and had same result as you had for expected. Only difference of few items did not had 0 (like contractors). The macro did not produce it and you had it.
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", "NonBillable 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.
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 NonBillable Expenses",0,IF($E29="NONBILLABLE DIRECT COSTS",M24+M25+M26+M27+M28, IF($E29="CONTRIBUTION",M19M27,IF($E29="Contribution %",(M28/M12)*100,IF($E29="Variance",M27M28,IF($E29="","",J29))))))
Note
Thanks to rizvisa1 for this tip on the forum.
 Excel  Using integers as cell reference
 Cell reference to locate /store data in excel ✓  Forum  Excel
 Excel Macro  using integers as cell referenc ✓  Forum  Excel
 Excel copy conditional formatting with relative cell references ✓  Forum  Excel
 Excel conditional formatting if cell contains any date  HowTo  Excel
 Excel if range of cells contains specific text then return value ✓  Forum  Excel