Overwriting data
 

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

--
Many Thanks

Sue

Overwriting data
 

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


"Sue" wrote:

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

--
Many Thanks

Sue

Overwriting data
 

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
--
jb


"Sue" wrote:

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

--
Many Thanks

Sue

Overwriting data
 

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" wrote:

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
--
jb


"Sue" wrote:

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

--
Many Thanks

Sue

Overwriting data
 

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

--
jb


"Sue" wrote:

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" wrote:

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
--
jb


"Sue" wrote:

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

--
Many Thanks

Sue

Overwriting data
 

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


Replace @mailinator.com with @tiscali.co.uk


"Sue" wrote in message
...
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

--
Many Thanks

Sue

Overwriting data
 

The easiest way of getting the last row is the following

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Sue" wrote in message
...
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

--
Many Thanks

Sue

Overwriting data
 

Joel" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk

Overwriting data
 

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Joel" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk

Overwriting data
 

Hi All

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

Sue


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Joel" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk

Overwriting data
 

"Sue" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Sue" wrote in message
...
Hi All

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

Sue


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Joel" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk