Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell.
In Vb, I am trying to set as the ActiveCell, the first empty multiple of ~ 30
in column A. I.E. If A1 = "one" A31 = "Example" the program will spot this and set as ActiveCell A61. PS Although setting this as active cell may not be the best way to do what I am trying to do, I quite enjoy finding out for myself how to do things and so I am not too worried how messy my code is. This has got me stumped though, I can't even think of a long winded way to do it. Not when I could potentially use all 65k rows. I'd very much appreciate all assistance. -- Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell.
Hi Davey,
Try: '============= Public Sub TesterA() Dim rngEmpty As Range Dim i As Long For i = 1 To Rows.Count Step 3 Set rngEmpty = Cells(i, "A") If IsEmpty(rngEmpty) Then Exit For End If Next i If Not rngEmpty Is Nothing Then MsgBox rngEmpty.Address End If End Sub '<<============= --- Regards, Norman "DaveyJones" wrote in message ... In Vb, I am trying to set as the ActiveCell, the first empty multiple of ~ 30 in column A. I.E. If A1 = "one" A31 = "Example" the program will spot this and set as ActiveCell A61. PS Although setting this as active cell may not be the best way to do what I am trying to do, I quite enjoy finding out for myself how to do things and so I am not too worried how messy my code is. This has got me stumped though, I can't even think of a long winded way to do it. Not when I could potentially use all 65k rows. I'd very much appreciate all assistance. -- Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell.
Sub FindEmptyCell()
For i = 1 To 65536 Step 30 Debug.Print i If IsEmpty(Cells(i, 1)) Then Cells(i, 1).Select Exit Sub End If Next End Sub -- Regards, Tom Ogilvy "DaveyJones" wrote: In Vb, I am trying to set as the ActiveCell, the first empty multiple of ~ 30 in column A. I.E. If A1 = "one" A31 = "Example" the program will spot this and set as ActiveCell A61. PS Although setting this as active cell may not be the best way to do what I am trying to do, I quite enjoy finding out for myself how to do things and so I am not too worried how messy my code is. This has got me stumped though, I can't even think of a long winded way to do it. Not when I could potentially use all 65k rows. I'd very much appreciate all assistance. -- Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell.
Hi Davey,
Please replace my code with the following version: '============= Public Sub TesterA() Dim rngEmpty As Range Dim i As Long For i = 1 To Rows.Count Step 3 If IsEmpty(Cells(i, "A")) Then Set rngEmpty = Cells(i, "A") Exit For End If Next i If Not rngEmpty Is Nothing Then MsgBox rngEmpty.Address End If End Sub '<<============= --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for first empty cell.
Thanks alot you guys, I've got it sorted now, i'm on version 6 of my program
though. V5 worked, but I didn't structure it so i don't even know whats going on and I wrote it ... -- Dave "Tom Ogilvy" wrote: Sub FindEmptyCell() For i = 1 To 65536 Step 30 Debug.Print i If IsEmpty(Cells(i, 1)) Then Cells(i, 1).Select Exit Sub End If Next End Sub -- Regards, Tom Ogilvy "DaveyJones" wrote: In Vb, I am trying to set as the ActiveCell, the first empty multiple of ~ 30 in column A. I.E. If A1 = "one" A31 = "Example" the program will spot this and set as ActiveCell A61. PS Although setting this as active cell may not be the best way to do what I am trying to do, I quite enjoy finding out for myself how to do things and so I am not too worried how messy my code is. This has got me stumped though, I can't even think of a long winded way to do it. Not when I could potentially use all 65k rows. I'd very much appreciate all assistance. -- Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells | Excel Discussion (Misc queries) | |||
Leaving an empty cell empty | Excel Discussion (Misc queries) | |||
why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
Finding next empty empty cell in a range of columns | Excel Programming | |||
Empty cell and a the empty String | Excel Programming |