Every 4th Row

[Sue]

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

 

[Joel]

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]

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

 

[Joel]

My code is not anything like the code you posted. The results may be the
same but my code will work if you explain what you need with some minor
changes.

There are three conditions you need to explain:

1) What do you want to do when Column C contains no data?
2) What do you want to do when Column C is partially filled?
3) What do you want to do when Column c is filled up to Row 97? Do you want
ot go beyond row 97, Delete some Rows, or Over-write Rows.

 

[Sue]

Hi Joel
Col E - Row97 - Cell has Data
ColE - Rows 96,95,94 - Cells are blank
ColE - Row93 - Cell to have Data inserted
ColE - Rows 92,91,90 - Cells are blank
And so on upto Row5

Doing it the way I did it and by adding another CommandButton

Private Sub Add6_Click()
Dim ws As Worksheet
Set ws = Worksheets("Result")
StartRowNum = 9 '<<<< instead of 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

however I would need 24 different CommandButtons - I would'nt remember
which one to click on - that is why I asked the question.

 

[Joel]

I guess you ae filling Up the spreadsheet so use this

Private Sub Add4_Click()
Dim ws As Worksheet
Set ws = Worksheets("Result")

StartRowNum = 9 '<<<< instead of 5
EndRowNum = 97


FirstRow = Range("B8").End(xldown).Row

If freerownum > EndRowNum Then
freerownum = EndRowNum
Else
freerownum = FirstRow - 4
End If

If freerownum < StartRow then
Msgbox("Table is Full - Can't Add Value")

Else
ws.Cells(freerownum, 5).Value = Trim(UserForm1.Cb2.Value) + " " & _
Trim(UserForm1.Tb40A.Value) + " " & _
Trim(UserForm1.Tb40B.Value) + " " & _
Trim(UserForm1.Tb40C.Value)
End If

End Sub



If you want to associated each item in the list box to a specific row then
use this code below. When the user selects the 1st item in the box it will
go into Row 9, then 2nd item in Row 13, 3rd in Row 17, ... Last Item in Row
97. You can add a check to prevent a user from selecting an item more than
once by checking the cell to see if there is data.

Private Sub Add6_Click()
Dim ws As Worksheet
Set ws = Worksheets("Result")
StartRowNum = 9 '<<<< instead of 5
EndRowNum = 97

selectedItem = UserForm1.Tb40B.listindex
RowNum = (4 * selectedItem) + 9

ws.Cells(RowNum, 5).Value = Trim(UserForm1.Cb2.Value) + " " &
Trim(UserForm1.Tb40A.Value) + " " & Trim(UserForm1.Tb40B.Value) + " " &
Trim(UserForm1.Tb40C.Value)