Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing info from userform to spreadsheet
Hey there~
I have a userform that has some combo/textboxes. Boxes a PO #, Date, Vendor, Salesperson, Amount, %. The user hits an "add" button and that information is is transposed into the appropriate spreadsheet, going to the last empty cell (bottom up) and inserting the PO #, then moves right to the next cell and places the date, then right and inserts the salesperson, then over for the P.O. Amount, and then the % belonging to that appropriate salesperson. The tricky part that I need help with is that I the code I have a case set up so that depending on the vendor, the appropriate column letter is chosen, and value of the (po amount * %) is placed in that vendor's column on the same row as the rest of the PO info. Well, when I run the code, the P.O. Amount is not copying over into the appropriate column. I am also concerned that it will not copy over laterally; By that I mean that I have not been able to get it into the appropriate column, but when I do I am afraid that it will not stay in , say, row 6 with the rest of the info, that it will go up to the first available cell, adn that wouldn't be good. Anyway, here is the code...can someone take a look and tell me what you think is a possible solution. Thanks. Private Sub cmdAdd_Click() Dim LastCell As Range With Sheets("Charlotte") Set LastCell = .Range("B65000").End(xlUp) If IsEmpty(LastCell) Then 'do nothing Else Set LastCell = LastCell.Offset(1, 0) End If LastCell.Value = txtPONumber.Value LastCell.Offset(0, 1) = txtPODate.Value LastCell.Offset(0, 2) = cmboSales.Value LastCell.Offset(0, 3) = txtPOAmount.Value LastCell.Offset(0, 4) = txtPOPercent.Value LastCell.Offset(0, 5) = ((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) Dim col As String Select Case cmboVendor.Value Case "Airguard": col = "H" Case "Calmac": col = "I" Case "Calmac-Polaris": col = "J" Case "Cambridgeport": col = "K" Case "CleanPak": col = "L" Case "Dell Corporation": col = "M" Case "Drykor": col = "N" Case "Edwards": col = "O" Case "Environmental Dynamics": col = "P" Case "Freidrich": col = "Q" Case "Good News Enterprise": col = "R" Case "Johnson": col = "S" Case "Koldwave": col = "T" Case "Reznor": col = "U" Case "Schwank": col = "V" Case "Synchroflo": col = "W" Case "Semco": col = "X" Case "Thycurb": col = "Y" Case "Data Aire": col = "AF" Case "Multistack": col = "AG" Case "Poolpak": col = "AH" End Select Range(col & 6).FormulaR1C1 = ((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) End With End Sub TIA David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing info from userform to spreadsheet
David,
'----- value of the (po amount * %) is placed in that vendor's column on the same row as the rest of the PO info Range(col & lastCell.row).FormulaR1C1 = .... '----- will not copy over laterally; By that I mean that I have not been able to get it into the appropriate column So, what happens? What is the value of col? Range(col & 6) or Range(col & lastCell.row) should work if the Select Case is OK. '----- Regards Anders Silven "David" skrev i meddelandet ... Hey there~ I have a userform that has some combo/textboxes. Boxes a PO #, Date, Vendor, Salesperson, Amount, %. The user hits an "add" button and that information is is transposed into the appropriate spreadsheet, going to the last empty cell (bottom up) and inserting the PO #, then moves right to the next cell and places the date, then right and inserts the salesperson, then over for the P.O. Amount, and then the % belonging to that appropriate salesperson. The tricky part that I need help with is that I the code I have a case set up so that depending on the vendor, the appropriate column letter is chosen, and value of the (po amount * %) is placed in that vendor's column on the same row as the rest of the PO info. Well, when I run the code, the P.O. Amount is not copying over into the appropriate column. I am also concerned that it will not copy over laterally; By that I mean that I have not been able to get it into the appropriate column, but when I do I am afraid that it will not stay in , say, row 6 with the rest of the info, that it will go up to the first available cell, adn that wouldn't be good. Anyway, here is the code...can someone take a look and tell me what you think is a possible solution. Thanks. Private Sub cmdAdd_Click() Dim LastCell As Range With Sheets("Charlotte") Set LastCell = .Range("B65000").End(xlUp) If IsEmpty(LastCell) Then 'do nothing Else Set LastCell = LastCell.Offset(1, 0) End If LastCell.Value = txtPONumber.Value LastCell.Offset(0, 1) = txtPODate.Value LastCell.Offset(0, 2) = cmboSales.Value LastCell.Offset(0, 3) = txtPOAmount.Value LastCell.Offset(0, 4) = txtPOPercent.Value LastCell.Offset(0, 5) = ((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) Dim col As String Select Case cmboVendor.Value Case "Airguard": col = "H" Case "Calmac": col = "I" Case "Calmac-Polaris": col = "J" Case "Cambridgeport": col = "K" Case "CleanPak": col = "L" Case "Dell Corporation": col = "M" Case "Drykor": col = "N" Case "Edwards": col = "O" Case "Environmental Dynamics": col = "P" Case "Freidrich": col = "Q" Case "Good News Enterprise": col = "R" Case "Johnson": col = "S" Case "Koldwave": col = "T" Case "Reznor": col = "U" Case "Schwank": col = "V" Case "Synchroflo": col = "W" Case "Semco": col = "X" Case "Thycurb": col = "Y" Case "Data Aire": col = "AF" Case "Multistack": col = "AG" Case "Poolpak": col = "AH" End Select Range(col & 6).FormulaR1C1 = ((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) End With End Sub TIA David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing info from userform to spreadsheet
You are putting your answer always in Row 6 Range(col & 6 ).FormulaR1C1 = ((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bringing info from userform to spreadsheet
cells(lastcell.row,col).FormulaR1C1 = _
((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) -- Regards, Tom Ogilvy David wrote in message ... Hey there~ I have a userform that has some combo/textboxes. Boxes a PO #, Date, Vendor, Salesperson, Amount, %. The user hits an "add" button and that information is is transposed into the appropriate spreadsheet, going to the last empty cell (bottom up) and inserting the PO #, then moves right to the next cell and places the date, then right and inserts the salesperson, then over for the P.O. Amount, and then the % belonging to that appropriate salesperson. The tricky part that I need help with is that I the code I have a case set up so that depending on the vendor, the appropriate column letter is chosen, and value of the (po amount * %) is placed in that vendor's column on the same row as the rest of the PO info. Well, when I run the code, the P.O. Amount is not copying over into the appropriate column. I am also concerned that it will not copy over laterally; By that I mean that I have not been able to get it into the appropriate column, but when I do I am afraid that it will not stay in , say, row 6 with the rest of the info, that it will go up to the first available cell, adn that wouldn't be good. Anyway, here is the code...can someone take a look and tell me what you think is a possible solution. Thanks. Private Sub cmdAdd_Click() Dim LastCell As Range With Sheets("Charlotte") Set LastCell = .Range("B65000").End(xlUp) If IsEmpty(LastCell) Then 'do nothing Else Set LastCell = LastCell.Offset(1, 0) End If LastCell.Value = txtPONumber.Value LastCell.Offset(0, 1) = txtPODate.Value LastCell.Offset(0, 2) = cmboSales.Value LastCell.Offset(0, 3) = txtPOAmount.Value LastCell.Offset(0, 4) = txtPOPercent.Value LastCell.Offset(0, 5) = ((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) Dim col As String Select Case cmboVendor.Value Case "Airguard": col = "H" Case "Calmac": col = "I" Case "Calmac-Polaris": col = "J" Case "Cambridgeport": col = "K" Case "CleanPak": col = "L" Case "Dell Corporation": col = "M" Case "Drykor": col = "N" Case "Edwards": col = "O" Case "Environmental Dynamics": col = "P" Case "Freidrich": col = "Q" Case "Good News Enterprise": col = "R" Case "Johnson": col = "S" Case "Koldwave": col = "T" Case "Reznor": col = "U" Case "Schwank": col = "V" Case "Synchroflo": col = "W" Case "Semco": col = "X" Case "Thycurb": col = "Y" Case "Data Aire": col = "AF" Case "Multistack": col = "AG" Case "Poolpak": col = "AH" End Select Range(col & 6).FormulaR1C1 = ((txtPOAmount.Value) * ((txtPOPercent.Value) / 100)) End With End Sub TIA David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bringing in colours from another spreadsheet | Excel Discussion (Misc queries) | |||
lookup function bringing in wrong info | Excel Worksheet Functions | |||
create a userform to populate a diagram with the forms info | Excel Discussion (Misc queries) | |||
userform for spreadsheet | Excel Discussion (Misc queries) | |||
Repost: Looking for info on how to program a userform to use filters | Excel Programming |