Overwriting data

[Sue]

Hi All

Can you help me out with the following code all it is doing is over writing
the data in Row 1 on the sheet - instead going to the next Row when entering
new data

Private Sub Add1_Click()
Dim rownum As Integer
Dim startrownum As Integer
Dim endrownum As Integer
Dim freerownum As Integer

startrownum = 2
endrownum = 250
For rownum = startrownum To endrownum

freerownum = rownum
rownum = endrownum

Next rownum
Sheets("Entrants").Range("A" & Trim(Str(freerownum))) =
Trim(UserForm2.Tb2.Value) + " " & Trim(UserForm2.Tb1.Value)
Sheets("Entrants").Range("B" & Trim(Str(freerownum))) = Tb3.Value
Sheets("Entrants").Range("C" & Trim(Str(freerownum))) = Tb4.Value

End Sub

 

[Joel]

Excel combines strings and number together removing leading blanks
automatically.
Use END to get last row and add data to next row as shown below

Private Sub Add1_Click()
Dim rownum As Integer
Dim startrownum As Integer
Dim endrownum As Integer
Dim freerownum As Integer

LastRow = Range("A" Rows.count).end(xlup).row
NewRow = LastRow + 1

Sheets("Entrants").Range("A" & NewRow) =
Trim(UserForm2.Tb2.Value) + " " & Trim(UserForm2.Tb1.Value)
Sheets("Entrants").Range("B" & NewRow) = Tb3.Value
Sheets("Entrants").Range("C" & NewRow) = Tb4.Value

End Sub

 

[john]

if you are just adding a new line of data then following may work:

Private Sub Add1_Click()
Dim freerownum As Long

With Worksheets("Entrants")
freerownum = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

.Range("A" & freerownum) = _
Trim(UserForm2.Tb2.Value) + " " & Trim(UserForm2.Tb1.Value)

.Range("B" & freerownum) = Tb3.Value

.Range("C" & freerownum) = Tb4.Value
End With
End Sub

 

[Sue]

Hi John

Thanks for the reply however as I have data below row 250 the data now goes
below row 250 that is why I was hoping it was possible to send the data down
from row 2 onwards instead of
-- With Worksheets("Entrants")

freerownum = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 (as is now)

Tried to use below all to no avail -- Is below possible
With Worksheets("Entrants")

freerownum = .Cells(.Rows.Count, "A").End(xlDown).Row + 1

Many Thanks

Sue

 

[john]

you want to place the data in rows 2 to 250? is that correct?

Probably much better way to write this but for ease I have added the FOR
loop back in to the code & hopefully, if I have understood correctly what you
are trying to do, it will do what you want - sorry in advance if I have got
this wrong!

Private Sub Add1_Click()
Dim freerownum As Long

With Worksheets("Entrants")
For freerownum = 2 To 250
If .Range("A" & freerownum).Value = "" Then

.Range("A" & freerownum) = _
Trim(UserForm2.Tb2.Value) + " " & Trim(UserForm2.Tb1.Value)

.Range("B" & freerownum) = Tb3.Value

.Range("C" & freerownum) = Tb4.Value
Exit For
End If
Next freerownum
End With
End Sub

 

[Sandy Mann]

If your loop is just to find the next empty row then try changing it to:

For rownum = startrownum To endrownum
If Cells(rownum, 1) <> "" Then
freerownum = rownum
Exit For
End If
Next rownum


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Joel]

The easiest way of getting the last row is the following

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

 

[Sandy Mann]

The easiest way of getting the last row is the following

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

Except the OP said in another post:
"Thanks for the reply however as I have data below row 250 "

so she is not looking for the last used Row. Additionally XL *remembers*
the last cell that you have entered data into even although you later delete
it, so it may not be the last Row of visible data.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Joel,

Re-reading my response to your post it seems a lot more terse and brusque
than I intended. I did not mean it to be a putdown of your suggestion, my
apologies if it sounded like one.

--
Kind regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sue]

Hi All

After viewing and trying all the options went with the Sandy Mann solution
Works OK for me

 

[Sandy Mann]

Hi All

After viewing and trying all the options went with the Sandy Mann solution
Works OK for me

Well I'm genuinely surprised at that!

Using:

For rownum = startrownum To endrownum
If Cells(rownum, 1) = "" Then
freerownum = rownum
Exit For
End If
Next rownum

I could understand but I don't know why I put the <> "" in that line unless
I was more tired than I thought!

Anyway, the whole For/Next loop can be replaced with one line:

freerownum = Cells(startrownum, 1).End(xlDown).Row + 1

Although to ensure that you never fill in more than to Row 250 you may want
to make it:

freerownum = Cells(startrownum, 1).End(xlDown).Row + 1
If freerownum > 250 Then
MsgBox "No room left!"
Exit Sub
End If


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk