Every 4th Row
Hi Joel
Thanks for your response however your reply is almost the same as what I
posted except it starts from row 97 -- if I refill the textboxes with data
from the listbox and click on Add4 it overwrites the contents on row 97
instead of placing it in row 93
--
Many Thanks
Sue
"Joel" wrote:
It was difficult from your code to exactly understand what you wanted. The
description of the posting says "Every 4th row" but yoiu code isn't doing
that. I used "END(XLUP) " to find the Last used row. Since this may be less
than row 5 I added a check to make sure if it was less than 5 to set it to 5.
I also added a check in case there was more data than 97 since your code
wasn't trying to do that. Whe 97 is reached I deleted Row 5 and added the
data to Row 97. Not sure if this is correct. I figured I would give you
some ideas on how to solve your problems. Delete the code you don't need.
Private Sub Add4_Click()
Dim ws As Worksheet
Set ws = Worksheets("Result")
LastRow = Range("B" & Rows.Count).End(xlUp).Row
freerownum = LastRow + 1
If freerownum < 5 Then
freerownum = 5
End If
If freerownum 97 Then
Rows(5).Delete
freerownum = 97
end if
ws.Cells(freerownum, 5).Value = Trim(UserForm1.Cb2.Value) + " " & _
Trim(UserForm1.Tb40A.Value) + " " & _
Trim(UserForm1.Tb40B.Value) + " " & _
Trim(UserForm1.Tb40C.Value)
End Sub
"Sue" wrote:
Hi All
Can you help me solve the following code problem that I found and altered to
run off my UserForm.
Private Sub Add4_Click()
Dim ws As Worksheet
Set ws = Worksheets("Result")
StartRowNum = 5
EndRowNum = 97
For rownum = StartRowNum To EndRowNum
If Trim(Sheets("Result").Range("B" & Trim(Str(rownum)))) = "" Then
freerownum = rownum
rownum = EndRowNum
End If
Next rownum
ws.Cells(freerownum, 5).Value = Trim(UserForm1.Cb2.Value) + " " &
Trim(UserForm1.Tb40A.Value) + " " & Trim(UserForm1.Tb40B.Value) + " " &
Trim(UserForm1.Tb40C.Value)
End Sub
I select the data from a ListBox and it fills the boxes above on click Add4
it places the data correctly on the sheet -- my problem is that when I select
the next data from the Listbox it overides the previous input -- each time I
fill the TextBoxes from the Listbox I need the data to enter another row in
the same column. For example after starting at Row5 ColE the next data I need
to enter in Row9 ColE then Row13 ColE - so basically its every 4th Row down
to 97. Hope I've explained it properly
--
Many Thanks
Sue
|