Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Every 4th Row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Every 4th Row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Every 4th Row
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Every 4th Row
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. -- Many Thanks Sue "Joel" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Every 4th Row
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) "Sue" wrote: 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. -- Many Thanks Sue "Joel" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|