Find the first available cell

[Eraengineer]

I'm looking for a macro that when I type in a number it places it in the first available cell in a range of cells. The range is C2:Z6. I need the number to be placed in C6 first, then C5 then C4, then C3, then C2, and then move to D6, then to D5, etc. Once range C2:Z6 has been filled the next availbe range will be C11:Z15. The same pattern must occur for range C11:Z15. I just need an example to get started. I am entering the number in a textbox on a form.

 

[MickG]

Try this:-

Private [COLOR=navy]Sub[/COLOR] TextBox1_LostFocus()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("c2: z2")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]For[/COLOR] n = 5 To 1 [COLOR=navy]Step[/COLOR] -1
        [COLOR=navy]If[/COLOR] Dn(n) = "" [COLOR=navy]Then[/COLOR]
            Dn(n) = TextBox1.Value
            [COLOR=navy]Exit[/COLOR] [COLOR=navy]Sub[/COLOR]
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick