View Full Version : Strange behavior of for... next loop

hanidraidi

06-15-2014, 01:50 PM

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

06-15-2014, 02:17 PM

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

06-15-2014, 02:23 PM

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

06-16-2014, 09:21 PM

Note the apostrophe in the formula

With Range("a1").Resize(150, 150)

.FormulaR1C1 = "=""'(""&ROW()&"",""&Column()&"")"""

.Value = .Value

End With

hanidraidi

06-19-2014, 04:06 AM

Thanks a lot, it worked fine!

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.