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.
2
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
0
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?
0
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.
0
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.
0
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.
0
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))))))
0