# Excel Macro - using integers as cell referenc

Solved/Closed
Andy May - Mar 22, 2010 at 03:19 PM
Andy May - Mar 24, 2010 at 11:06 AM
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 Non-Billable Expenses" Then
Cell y,x = 0
Else
If Cell a,x = "NON-BILLABLE DIRECT COSTS" Then
Cell y,x = Cell y,x-5 + Cell y,x-4 + Cell y,x-3 + Cell y,x-2 + Cell y,x-1
Else
If Application.IsText(a, x) = "CONTRIBUTION" Then
Cell y,x = Cell y,x-10 - Cell y,x-2
Else
If Cell a,x = "Contribution %" Then
Cell y,x = (Cell y,x-1 / Cell y,x-17) * 100
Else
If Cell a,x = "Variance" Then
Cell y,x = Cell y,x-2 - Cell y,x-1
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.

## 2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 22, 2010 at 05:20 PM
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.
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 23, 2010 at 07:43 AM
This will give you good idea. You need to modify it. Also remember, it is cells(row, column)

Sub Tester()

Dim a As Integer
Dim x As Integer
Dim y As Integer

Dim cellValue As String 'to capture the value of a cell to be used in select case statement

'**** 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

cellValue = Cells(x, "E")

'**** 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:

' insead of if else if.... else if... esle end if. using select case
Select Case cellValue

Case Is = "Other Non-Billable Expenses"

Cells(x, y) = 0

Case Is = "NON-BILLABLE DIRECT COSTS"
If (x > 5) Then Cells(x, y) = Cells(x, y) + Cells(x - 5, y)
If (x > 4) Then Cells(x, y) = Cells(x, y) + Cells(x - 4, y)
If (x > 3) Then Cells(x, y) = Cells(x, y) + Cells(x - 3, y)
If (x > 2) Then Cells(x, y) = Cells(x, y) + Cells(x - 2, y)
If (x > 1) Then Cells(x, y) = Cells(x, y) + Cells(x - 1, y)

Case Is = "CONTRIBUTION"

If x > 10 Then
Cells(x, y) = Cells(x - 10, y) - Cells(x - 2, y)
End If

Case Is = "Contribution %"
If (x > 17) Then
Cells(x, y) = Cells(x - 1, y) * Cells(x - 17, y)
End If

Case Is = "Variance"
If (x > 2) Then
Cells(x, y) = Cells(x - 2) - Cells(x - 1, y)
End If

Case Is = ""
Cells(x, y) = ""

Case Else
Cells(x, y) = Cells(x, "J")

End Select
Next x
Next y

End Sub
Thanks for the tip on Case Is. I've never seen that before.
I get the "Invalid Next control variable reference" error when using the above code.

So, I modified it slightly so that (I think) the code will go through all x's before going to the next y:
End Select
If x = totalrows Then
Next y
x = 1
Else
Next x

But then I get the "Next without For" error when using. Any ideas how to solve?
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Mar 23, 2010 at 12:05 PM
Andy

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.
Here's the file:
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.
You can see the 2nd macro in there, that one was just to test the statements without FOR / NEXT involved.

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