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

Clip_161.jpgClip_162.jpg

[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!