# Excel Macro - using integers as cell referenc

Solved/Closed
Related:

- Excel Macro - using integers as cell referenc
- Excel macro delete row if cell is blank ✓ - Forum - Excel
- Excel macro to create new sheet based on value in cells - Guide
- Excel macro to create new workbook based on value in cells ✓ - Forum - Excel
- Macro to Create New Workbook and Copy Data at Each Change of X - Forum - Excel
- Excel macro send email to address in cell ✓ - Forum - Excel

## 2 replies

rizvisa1

Mar 22, 2010 at 05:20 PM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

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.

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.

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

Mar 23, 2010 at 07:43 AM

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

Mar 23, 2010 at 11:28 AM

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?

Mar 23, 2010 at 12:05 PM

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.

Mar 23, 2010 at 04:10 PM

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.

Mar 23, 2010 at 04:12 PM

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.