Bringing info from userform to spreadsheet

[David]

Hey there~
I have a userform that has some combo/textboxes. Boxes
are: 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

 

[Anders S]

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

 

[mudraker]

You are putting your answer always in Row 6


Range(col & 6 ).FormulaR1C1 =
((txtPOAmount.Value) * ((txtPOPercent.Value) / 100))

 

[Tom Ogilvy]

cells(lastcell.row,col).FormulaR1C1 = _
((txtPOAmount.Value) * ((txtPOPercent.Value) / 100))