Using loops in VBA

I had a look and got some answers. I have found out how to get into the VB code and create a command button. I want to get a series of numbers across a row (say, 1 to ten), with an empty cell between each number. I wrote this code line to get my first number:

Sheet1.cells(10,5) = "1"

and that worked, so i copied 9 more times and just changed the cell number and the number i wanted in it, eg

Sheet1.cells(10,7) = "2"

and that worked of course, but is obviously tedious and ridiculously slow if i wanted 1 to 100

So, sorry for the simple question: but how do i get it to do a whole series of numbers with one command? I tried

Sheet1.cells(10,5 to 15) = "1 to 10"

and it didn't work. Tried dashes instead of the word to... then thought, oh heck, stupid as it looks in a forum, I'm going to ask... I'm thinking there is an If loop that would do it for me... am i on the right track?

You could use something like the following. I added a few comments so you can see what numbers to change to alter the start/end numbers and starting column. You could also skip 3 cells instead of 2 by changing "j=j+2" to "j=j+3", for example.

Sub insertnumbers()
Dim i As Long, j As Long
j = 5 ' Starting column, e.g. 5 is E, 8 is H, etc.

Application.ScreenUpdating = False
For i = 1 To 20 ' Replace 1 and 20 with your actual first and last numbers in sequence
    Cells(10, j).Value = i
    j = j + 2
Next i
Application.ScreenUpdating = True
End Sub

Hey, Paul! That worked a treat. I had to take out the sub and end sub when i pasted it into my command button... didn't like that there were two of those... I didnt realise you could dimension something just as Long. I see that j is the column so thats useful to know to just go j = j + 1 or 2 or 3 to get it to skip columns and i assume i can do the same thing with rows. I see its a For next loop that you created and i see that i can change the values so if i want to go up to say a hundred I can. I am trying to not just copy and paste but learn from you as I go. Im a bit hazy about the Application.ScreenUpdating command. does setting it to false at the beginning clear everything or something? and then setting it as true means it updates to the numbers I want?

thanks again for helping me all the way from Wisconsin (I'm in Australia so we are going into winter but its probably not much colder than your summers
I have more questions but I will try to work things out first and only bother you when i get stuck.
cheers from downundah

Hi Reef, glad it worked for you and that you understand how to update the variables as needed for your sheets. Sorry about not mentioning to remove my Sub/EndSub headers - it crossed my mind but I forgot by the time I hit reply.

As for "Application.ScreenUpdating", setting it to False simply tells the application not to update the window until ScreenUpdating is set back to True. Everything is still running and calculating as normal, you just don't see it. It reduces screen flicker, and can actually dramatically increase the speed of loops (for longer data sets, not really something like this).

Yo Paul, ok the mystery of app.screenupdating is solved. Since i will be working with long sets down the track, that is useful to know.
My next programming feat is a leetle more complicated.

I have a series of numbers in column 3 that come from some studies of animals i have done over the years... I'm a biologist and I do a lot of watching and recording... basically the numbers 0 to ten and so on represent behaviours: 0 is doing nothing, 1 is moving around, 2 is eating, 3 drinking, 4 preening (if its birds I am watching), 5 is defecating 6 is copulating etc...

What I want to know from a sequence is the subsequent action after each action. So in a sequence, what is the most likely action after eating? That is what your row of numbers is for... it goes through the column, takes the first number eg 0 and its followed by a 5, so it goes over to the column with 0 in row ten, goes down 2 rows and puts in the number 5... then it goes back round to the second number in the sequence, which must be 5 and looks at the third number eg 3... then it runs over to the column with 5 in it, goes down 2 and puts in the number 3 etc etc.

So I studied the different kinda loops, decided I would use a for next loop, slept on the problem and had a go at the code this morning and this is what I came up with:

Private Sub CommandButton1_Click()
'loop to find the subsequent numbers of all numbers in a series
Dim ii As Long, jj As Long, kk As Long, ll As Long
Dim k As Long, j As Long, i As Long
jj = 3 'starting column is C
kk = 5 'starting row is 5
k = k + 2
For ii = Sheet1.Cells(jj, kk) To Sheet1.Cells(jj, kk + 200)
    ll = Sheet1.Cells(jj, kk + 1)
    Sheet1.Cells(k, ii).Value = ll
    kk = kk + 1
Next ii

End Sub

The line it doesnt like is: Sheet1.Cells(k, ii).Value = ll

I tried it several different ways and really thought this should work but it didn't and I don't know why...but I bet somebody here does!

Thanks again, Reef

reefswaggie,

I'm not convinced you need VBA for this task. Attached is an example workbook based on the criteria you described.

In column C is a list of random numbers between 0 and 9 going from C5 to C1004 (so there are 1000 random numbers acting as Action ID's as you described).

In cell F11 and copied over and down to F211 is this array formula:

=IFERROR(INDEX($C$6:$C$1004,SMALL(IF($C$5:$C$1003=F$10,ROW(INDIRECT("1:"&COUNTA($C$5:$C$1003)))),ROW()-ROW(F$10)))," ")

Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter.

That array formula gets the following action ID's for each action. So it gets every action ID that followed action 0 from the list in column C.

Then, to show the most common action that came after each action ID, cell F8 and copied over contains this regular (non-array) formula:

=INDEX(F11:F211,MATCH(MAX(INDEX(COUNTIFS(F11:F211,"<> ",F11:F211,F11:F211),)),INDEX(COUNTIFS(F11:F211,"<> ",F11:F211,F11:F211),),0))