For & next: what is the relationship

[TOM M]

Hello,

I am new to writing VBA, can you tell me the basic relationship between for & next? in this example i don't uderstand why i am being told "for without next" If too busy to explain this, can you suggest somewhere i could read about it?

Sub test()
For i = 1 To i = 5000: For X = 1 To X = 50
Cells(i, X) = Cells(i, X)
Next


End Sub

Many Thanks

Tom M

 

[BC...]

You have 2 For's. I commented that line starting at the : and it runs.

 

[jbeaucaire]

Proper visual layout may demonstrate the issue. FOR and NEXT always come in pairs. It appears you tried to nest 1 FOR NEXT inside of another and didn't finish one of them:

Sub test()
For i = 1 To 5000
    For X = 1 To 50
        Cells(i, X) = Cells(i, X)
    Next X
Next i

Now it doesn't appear you're code actually changes anything, so if this is just a learning exercise, then that's your answer.

Loops are slow. Once you learn to get them working try not to be enamoured by them. Evaluating every row (or in your case every cell) for something specific can sometimes be accomplished without a loop, potentially making your macro much faster. Keep that in mind when you move to actual code doing actual work for you.

Also, using a RANGE you can eliminate the inner loop:

Sub test()
For i = 1 To 5000
    Range(Cells(i, 1), Cells(i, 50)) = Range(Cells(i, 1), Cells(i, 50))
Next i
End Sub

....and using a range we can eliminate the loop altogether:

Sub test()
    Range(Cells(1, 1), Cells(5000, 50)) = Range(Cells(1, 1), Cells(5000, 50))
End Sub

 

[TOM M]

Hi guys,

Thanks very much for that, foolish mistake. I know it appears to do nothing, it is the sollution to a strange problem i am having. I have a system that exports into excel. once it exports, even if you change the format of a cell, it won't recognise the change until you go into every cell and leave it agin. any ideas?

 

[jbeaucaire]

...any ideas?

1) Use the 3rd macro I showed if you want the VBA solution to be applied.

2) Double check the import-to-excel process and insure the data type is properly indicated for all the columns of data, perhaps it is an incorrect data type that is causing the issue.

 

[BC...]

In the past I've used find and replace to get excel to "fire and event" like you mention. You could also use Text to Columns under the Data menu.