PDA

View Full Version : loop to go through a list find an empty cell and then place something in the cell



mtomecki
07-01-2014, 11:41 PM
Here is the code i'm using, gets all the way to

Private Sub CommandButton4_Click()


Dim i As Integer
Dim Value As Integer
i = 1
While Not IsEmpty(cell(i, 1))
i = i + 1
Wend
Value = i
cell.Value = newuser

OG Loc
07-02-2014, 02:08 AM
Try:


Private Sub CommandButton4_Click()

Dim i As Integer

i = 1
While Not IsEmpty(cells(i, 1))
i = i + 1
Loop
cells(i,1) = "newuser"

End Sub

I have assumed you want the new cell to have "newuser" written in it. You didn't need the thing called value, and as a general point it is bad practice to call a variable 'value' as value is the name of the procedure in VBA built in, which it looked like you were trying to use, but you didn't have it quite right (I could have used cells(i,1).value = "newusuer" in the code, but in general if you don't put .value after an object it assumes that is what you meant).

Bob Phillips
07-02-2014, 04:51 AM
No looping required


Private Sub CommandButton4_Click()
ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Value = "some value"
End Sub

mtomecki
07-02-2014, 08:58 PM
Cheers both worked a treat.

Zack Barresse
07-04-2014, 07:51 PM
Unless cell A1 is already blank, in which case the End method isn't a reliable solution. You could feasibly use SpecialCells, assuming you don't have too many unique blank regions, or if you could specify the range to just the desired cells, you could use something like this...

Const EnterValue As String = "Some value"

Dim FirstBlankCell As Range

On Error Resume Next
Set FirstBlankCell = Range("A:A").SpecialCells(xlCellTypeBlanks)(1, 1)
On Error GoTo 0
If Not FirstBlankCell Is Nothing Then
FirstBlankCell = EnterValue
End If

HTH