Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
I have an order form that has two sections for parts to be ordered
example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
If Range("A1").Value = "" Then
iNextRow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then iNextRow = Cells(Rows.Count,"A").End(xlUp).Row iColumn = 1 ElseIf Range("G1").Value = "" iNextRow = 1 iColumn = 7 Else iNextRow = Cells(Rows.Count,"G").End(xlUp).Row iColumn = 7 End If Cells(iNextRow, iColumn).Value = TextBox1.Value -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... I have an order form that has two sections for parts to be ordered example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
Thank You for your reply bob,
this works great but I need to be able to enter data into A1,A2,A3,A4 and so on until A15 is filled with data then go to G1. I tried to modify a little (and I will keep trying ) but this is only changing the active cell A1 Thanks again for your time and effort "Bob Phillips" wrote: If Range("A1").Value = "" Then iNextRow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then iNextRow = Cells(Rows.Count,"A").End(xlUp).Row iColumn = 1 ElseIf Range("G1").Value = "" iNextRow = 1 iColumn = 7 Else iNextRow = Cells(Rows.Count,"G").End(xlUp).Row iColumn = 7 End If Cells(iNextRow, iColumn).Value = TextBox1.Value -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... I have an order form that has two sections for parts to be ordered example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
This is what I've come up with so far let me know if I'm going in the right
direction hereis the code that you wrote out with my addition. Private Sub CommandButton1_Click() Nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlUp).Row iColumn = 1 Cells(Nextrow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlUp).Row iColumn = 7 End If Cells(inextrow, iColumn).Value = TextBox1.Value End Sub this will go to the next cell but it alos changes the previous cell right above it?? "hellZg8" wrote: Thank You for your reply bob, this works great but I need to be able to enter data into A1,A2,A3,A4 and so on until A15 is filled with data then go to G1. I tried to modify a little (and I will keep trying ) but this is only changing the active cell A1 Thanks again for your time and effort "Bob Phillips" wrote: If Range("A1").Value = "" Then iNextRow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then iNextRow = Cells(Rows.Count,"A").End(xlUp).Row iColumn = 1 ElseIf Range("G1").Value = "" iNextRow = 1 iColumn = 7 Else iNextRow = Cells(Rows.Count,"G").End(xlUp).Row iColumn = 7 End If Cells(iNextRow, iColumn).Value = TextBox1.Value -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... I have an order form that has two sections for parts to be ordered example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
What I gave you was based upon entering in A1:A15 until full, then going to
G1 etc. You must be consistent in your variable names. You are using NextRow one minute, inextrow the next. They are not the same. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... This is what I've come up with so far let me know if I'm going in the right direction hereis the code that you wrote out with my addition. Private Sub CommandButton1_Click() Nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlUp).Row iColumn = 1 Cells(Nextrow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlUp).Row iColumn = 7 End If Cells(inextrow, iColumn).Value = TextBox1.Value End Sub this will go to the next cell but it alos changes the previous cell right above it?? "hellZg8" wrote: Thank You for your reply bob, this works great but I need to be able to enter data into A1,A2,A3,A4 and so on until A15 is filled with data then go to G1. I tried to modify a little (and I will keep trying ) but this is only changing the active cell A1 Thanks again for your time and effort "Bob Phillips" wrote: If Range("A1").Value = "" Then iNextRow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then iNextRow = Cells(Rows.Count,"A").End(xlUp).Row iColumn = 1 ElseIf Range("G1").Value = "" iNextRow = 1 iColumn = 7 Else iNextRow = Cells(Rows.Count,"G").End(xlUp).Row iColumn = 7 End If Cells(iNextRow, iColumn).Value = TextBox1.Value -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... I have an order form that has two sections for parts to be ordered example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
Forgive me this is a new one on me I am Still trying to learn this coding
but the code you wrote only entered data into A1 nothing else unless I manually entered data in then it would got to G1 and stay there and change only the value in G1. the code below is what I have now (Your code with some additions) ' ' NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlDown).Row iColumn = 1 Cells(NextRow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlDown).Row iColumn = 7 Cells(NextRow, 7) = TextBox1.Value End If Cells(inextrow, iColumn).Value = TextBox1.Value Now once A1 has data it skips A2 and goes to the next A3, after that it stays in line until A15 then goes to G1 but stays there and only changes the value. "Bob Phillips" wrote: What I gave you was based upon entering in A1:A15 until full, then going to G1 etc. You must be consistent in your variable names. You are using NextRow one minute, inextrow the next. They are not the same. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... This is what I've come up with so far let me know if I'm going in the right direction hereis the code that you wrote out with my addition. Private Sub CommandButton1_Click() Nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlUp).Row iColumn = 1 Cells(Nextrow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlUp).Row iColumn = 7 End If Cells(inextrow, iColumn).Value = TextBox1.Value End Sub this will go to the next cell but it alos changes the previous cell right above it?? "hellZg8" wrote: Thank You for your reply bob, this works great but I need to be able to enter data into A1,A2,A3,A4 and so on until A15 is filled with data then go to G1. I tried to modify a little (and I will keep trying ) but this is only changing the active cell A1 Thanks again for your time and effort "Bob Phillips" wrote: If Range("A1").Value = "" Then iNextRow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then iNextRow = Cells(Rows.Count,"A").End(xlUp).Row iColumn = 1 ElseIf Range("G1").Value = "" iNextRow = 1 iColumn = 7 Else iNextRow = Cells(Rows.Count,"G").End(xlUp).Row iColumn = 7 End If Cells(iNextRow, iColumn).Value = TextBox1.Value -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... I have an order form that has two sections for parts to be ordered example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
I repeat
You must be consistent in your variable names. You are using NextRow one minute, inextrow the next. They are not the same. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... Forgive me this is a new one on me I am Still trying to learn this coding but the code you wrote only entered data into A1 nothing else unless I manually entered data in then it would got to G1 and stay there and change only the value in G1. the code below is what I have now (Your code with some additions) ' ' NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlDown).Row iColumn = 1 Cells(NextRow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlDown).Row iColumn = 7 Cells(NextRow, 7) = TextBox1.Value End If Cells(inextrow, iColumn).Value = TextBox1.Value Now once A1 has data it skips A2 and goes to the next A3, after that it stays in line until A15 then goes to G1 but stays there and only changes the value. "Bob Phillips" wrote: What I gave you was based upon entering in A1:A15 until full, then going to G1 etc. You must be consistent in your variable names. You are using NextRow one minute, inextrow the next. They are not the same. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... This is what I've come up with so far let me know if I'm going in the right direction hereis the code that you wrote out with my addition. Private Sub CommandButton1_Click() Nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlUp).Row iColumn = 1 Cells(Nextrow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlUp).Row iColumn = 7 End If Cells(inextrow, iColumn).Value = TextBox1.Value End Sub this will go to the next cell but it alos changes the previous cell right above it?? "hellZg8" wrote: Thank You for your reply bob, this works great but I need to be able to enter data into A1,A2,A3,A4 and so on until A15 is filled with data then go to G1. I tried to modify a little (and I will keep trying ) but this is only changing the active cell A1 Thanks again for your time and effort "Bob Phillips" wrote: If Range("A1").Value = "" Then iNextRow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then iNextRow = Cells(Rows.Count,"A").End(xlUp).Row iColumn = 1 ElseIf Range("G1").Value = "" iNextRow = 1 iColumn = 7 Else iNextRow = Cells(Rows.Count,"G").End(xlUp).Row iColumn = 7 End If Cells(iNextRow, iColumn).Value = TextBox1.Value -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... I have an order form that has two sections for parts to be ordered example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nextrow in another column
Bob Thank you for your time and effort.
I will keep plugging away at it "Bob Phillips" wrote: I repeat You must be consistent in your variable names. You are using NextRow one minute, inextrow the next. They are not the same. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... Forgive me this is a new one on me I am Still trying to learn this coding but the code you wrote only entered data into A1 nothing else unless I manually entered data in then it would got to G1 and stay there and change only the value in G1. the code below is what I have now (Your code with some additions) ' ' NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlDown).Row iColumn = 1 Cells(NextRow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlDown).Row iColumn = 7 Cells(NextRow, 7) = TextBox1.Value End If Cells(inextrow, iColumn).Value = TextBox1.Value Now once A1 has data it skips A2 and goes to the next A3, after that it stays in line until A15 then goes to G1 but stays there and only changes the value. "Bob Phillips" wrote: What I gave you was based upon entering in A1:A15 until full, then going to G1 etc. You must be consistent in your variable names. You are using NextRow one minute, inextrow the next. They are not the same. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... This is what I've come up with so far let me know if I'm going in the right direction hereis the code that you wrote out with my addition. Private Sub CommandButton1_Click() Nextrow = Application.WorksheetFunction.CountA(Range("A:A")) + 1 If Range("A1").Value = "" Then inextrow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then inextrow = Cells(Rows.Count, "A").End(xlUp).Row iColumn = 1 Cells(Nextrow, 1) = TextBox1.Value ElseIf Range("G1").Value = "" Then inextrow = 1 iColumn = 7 Else inextrow = Cells(Rows.Count, "G").End(xlUp).Row iColumn = 7 End If Cells(inextrow, iColumn).Value = TextBox1.Value End Sub this will go to the next cell but it alos changes the previous cell right above it?? "hellZg8" wrote: Thank You for your reply bob, this works great but I need to be able to enter data into A1,A2,A3,A4 and so on until A15 is filled with data then go to G1. I tried to modify a little (and I will keep trying ) but this is only changing the active cell A1 Thanks again for your time and effort "Bob Phillips" wrote: If Range("A1").Value = "" Then iNextRow = 1 iColumn = 1 ElseIf Range("A15").Value = "" Then iNextRow = Cells(Rows.Count,"A").End(xlUp).Row iColumn = 1 ElseIf Range("G1").Value = "" iNextRow = 1 iColumn = 7 Else iNextRow = Cells(Rows.Count,"G").End(xlUp).Row iColumn = 7 End If Cells(iNextRow, iColumn).Value = TextBox1.Value -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "hellZg8" wrote in message ... I have an order form that has two sections for parts to be ordered example A: #,B:Part #,C:Description,D:Qty G:#,H:Part #,I: Description,J:Qty each set of four columns has approximately 15 rows of data,i need to determine the end of the first column set and enter data into the next. once columns A1:A15 has it's data got to G1 and start entering data from a user form. I'm not sure if an array might work best for this or what ??? thank you in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for a column based on the column header and then past data from it to another column in another workbook | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Programming | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
How can i have all alike product codes in column A be matched with like cities in column B and then add the totals that are in column C | Excel Programming | |||
What's wrong with my NextRow code? | Excel Programming |