Strange behavior of for... next loop

[hanidraidi]

I am using a nested loop that finds a 150*150 matrix which determines the row and column number of each cell [e.g. cell D4 is (4, 4)]. This works fine but when cells reach columns greater than 99, strange results appears; the row number becomes minus, and the parentheses disappear. This is shown in the screenshot below.
I am using Excel 2013, and here is the code in the vbe


Option Explicit
Sub m()
Dim i, j As Integer
For i = 1 To 150
For j = 1 To 150
Cells(i, j) = "(" & i & "," & j & ")"
Next j
Next i
End Sub

Regards,

Hani

1182311824

[Paul_Hossler]

As written, 2010 does the same

When you try to enter I = 1 and J = 100 Excel sees "(1,100)" as negative 1,100

Something like this works



Option Explicit
Sub m()
Dim i As Long, j As Long ' BTW, the way you had it the I was dimmed as a variant, since you didn't explicitly say 'As Long'
For i = 1 To 150
For j = 1 To 150
ActiveSheet.Cells(i, j).NumberFormat = "@"
ActiveSheet.Cells(i, j).Value = "(" & i & "," & j & ")"
Next j
Next I
End Sub

[fredlo2010]

I agree with Paul. This is my try. I also added a little something to make it slightly faster to perform.


Sub m()
Dim i As Integer
Dim j As Integer
Dim arr(1 To 150, 1 To 150) As Variant

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' Load the array. Note the single quote in front of
' the parenthesis.
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
arr(i, j) = CStr("'(" & i & "," & j & ")")
Next j
Next i

' Add values to the range in one step
Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr


' To remove the error "Number stored as text"
Application.ErrorCheckingOptions.NumberAsText = False


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Thanks

[mikerickson]

Note the apostrophe in the formula

With Range("a1").Resize(150, 150)
.FormulaR1C1 = "=""'(""&ROW()&"",""&Column()&"")"""
.Value = .Value
End With

[hanidraidi]

Thanks a lot, it worked fine!