Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > LastCell Function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default LastCell Function
Hey everyone,

I am using the lastcell function to take input from a user
form and place it into a spreadsheet that accumulates
entry by entry and the lastcell function allows me to work
from the bottom up. The problem that I am facing is that
not every column will have an entry, so when I go to the
next line, it moves up a few rows to a couple of entries
prior. So I need help on how to program it so that it
finds the last cell and then just works its way to the
right from there. So say it finds that A12 was the last
entry, it will offset by one and then I would like for it
to work its way to the right, like offset(1,0) or
something. Here is the code that I am working with.

Thanks in advance for any help or direction that you can
give.

Private Sub cmdAdd_Click()

Dim LastCell As Range

Sheets("Charlotte").Activate
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = txtPONumber.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPODate.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = cmboSales.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOPercent.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
With ActiveSheet

If cmboVendor.Value = "Airguard" Then

Set LastCell = .Cells(.Rows.Count, "h").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else

With ActiveSheet
If cmboVendor.Value = "Calmac" Then

Set LastCell = .Cells(.Rows.Count, "i").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Calmac-Polaris" Then

Set LastCell = .Cells(.Rows.Count, "j").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Cambridgeport" Then

Set LastCell = .Cells(.Rows.Count, "k").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "CleanPak" Then

Set LastCell = .Cells(.Rows.Count, "l").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
MsgBox "still in process", vbOKOnly
End If
End With
End If
End With
End If
End With
End If
End With
End If
End With

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 57
Default LastCell Function
David,

If your data sheet is nice and clean, you can try the UsedRange, like in

'-----
Sub rngTest()
Dim lastCell As Range
With ActiveSheet.UsedRange
Set lastCell = .Rows(.Rows.Count).Cells(1).Offset(1, 0)
End With
lastCell.Value = txtPONumber.Value
lastCell.Offset(0, 1).Value = txtPODate.Value
lastCell.Offset(0, 2).Value = cmboSales.Value
lastCell.Offset(0, 3).Value = txtPOAmount.Value
lastCell.Offset(0, 4).Value = txtPOPercent.Value
lastCell.Offset(0, 5).Value = ((txtPOAmount.Value) * _
((txtPOPercent.Value) / 100))
End Sub
'-----

HTH
Anders Silven

"David" skrev i meddelandet ...
Hey everyone,

I am using the lastcell function to take input from a user
form and place it into a spreadsheet that accumulates
entry by entry and the lastcell function allows me to work
from the bottom up. The problem that I am facing is that
not every column will have an entry, so when I go to the
next line, it moves up a few rows to a couple of entries
prior. So I need help on how to program it so that it
finds the last cell and then just works its way to the
right from there. So say it finds that A12 was the last
entry, it will offset by one and then I would like for it
to work its way to the right, like offset(1,0) or
something. Here is the code that I am working with.

Thanks in advance for any help or direction that you can
give.

Private Sub cmdAdd_Click()

Dim LastCell As Range

Sheets("Charlotte").Activate
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = txtPONumber.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPODate.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = cmboSales.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOPercent.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
With ActiveSheet

If cmboVendor.Value = "Airguard" Then

Set LastCell = .Cells(.Rows.Count, "h").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else

With ActiveSheet
If cmboVendor.Value = "Calmac" Then

Set LastCell = .Cells(.Rows.Count, "i").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Calmac-Polaris" Then

Set LastCell = .Cells(.Rows.Count, "j").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Cambridgeport" Then

Set LastCell = .Cells(.Rows.Count, "k").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "CleanPak" Then

Set LastCell = .Cells(.Rows.Count, "l").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
MsgBox "still in process", vbOKOnly
End If
End With
End If
End With
End If
End With
End If
End With
End If
End With

End Sub
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 391
Default LastCell Function
this might get you started:-


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
Set LastCell = LastCell.Offset(0, 2) = cmboSales.Value
Set LastCell = LastCell.Offset(0, 3) =
txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 4) =
txtPOPercent.Value
Set LastCell = 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 Else
MsgBox "still in process", vbOKOnly
End Select

End With

End Sub


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hey everyone,

I am using the lastcell function to take input from a
user
form and place it into a spreadsheet that accumulates
entry by entry and the lastcell function allows me to
work
from the bottom up. The problem that I am facing is that
not every column will have an entry, so when I go to the
next line, it moves up a few rows to a couple of entries
prior. So I need help on how to program it so that it
finds the last cell and then just works its way to the
right from there. So say it finds that A12 was the last
entry, it will offset by one and then I would like for
it
to work its way to the right, like offset(1,0) or
something. Here is the code that I am working with.

Thanks in advance for any help or direction that you can
give.

Private Sub cmdAdd_Click()

Dim LastCell As Range

Sheets("Charlotte").Activate
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = txtPONumber.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPODate.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = cmboSales.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOPercent.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
With ActiveSheet

If cmboVendor.Value = "Airguard" Then

Set LastCell = .Cells(.Rows.Count, "h").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else

With ActiveSheet
If cmboVendor.Value = "Calmac" Then

Set LastCell = .Cells(.Rows.Count, "i").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Calmac-Polaris" Then

Set LastCell = .Cells(.Rows.Count, "j").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Cambridgeport" Then

Set LastCell = .Cells(.Rows.Count, "k").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "CleanPak" Then

Set LastCell = .Cells(.Rows.Count, "l").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
MsgBox "still in process", vbOKOnly
End If
End With
End If
End With
End If
End With
End If
End With
End If
End With

End Sub
.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 172
Default LastCell Function
Without going through all of your code. And not testing :

Set LastCell = .Cells(.Rows.Count, "B").End(xlUp).Offset(1,0)
With LastCell
.Value = txtPONumber.Value
.Offset(0, 1).Value = txtPODate.Value
.Offset(0, 2).Value = cmboSales.Value
.Offset(0, 3).Value = txtPOAmount.Value
.Offset(0, 4) = txtPOPercent.Value
.Offset(0, 5) = ((txtPOAmount.Value) * _
((txtPOPercent.Value) / 100))
If cmboVendor.Value = "Airguard" Then
.Offset(0, 6).Value = ((txtPOAmount.Value) * _
((txtPOPercent.Value) / 100))
ElseIf cmboVendor.Value = "Calmac" Then
.Offset(0, 7).Value = ((txtPOAmount.Value) * _
((txtPOPercent.Value) / 100))
ElseIf
' etc.
End If
End With

-----------------------------------------------------------------------------------

In other words, set a reference to LastCell once, and use OffSet from
there.

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
Hey everyone,

I am using the lastcell function to take input from a user
form and place it into a spreadsheet that accumulates
entry by entry and the lastcell function allows me to work
from the bottom up. The problem that I am facing is that
not every column will have an entry, so when I go to the
next line, it moves up a few rows to a couple of entries
prior. So I need help on how to program it so that it
finds the last cell and then just works its way to the
right from there. So say it finds that A12 was the last
entry, it will offset by one and then I would like for it
to work its way to the right, like offset(1,0) or
something. Here is the code that I am working with.

Thanks in advance for any help or direction that you can
give.

Private Sub cmdAdd_Click()

Dim LastCell As Range

Sheets("Charlotte").Activate
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = txtPONumber.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPODate.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = cmboSales.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOPercent.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
With ActiveSheet

If cmboVendor.Value = "Airguard" Then

Set LastCell = .Cells(.Rows.Count, "h").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else

With ActiveSheet
If cmboVendor.Value = "Calmac" Then

Set LastCell = .Cells(.Rows.Count, "i").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Calmac-Polaris" Then

Set LastCell = .Cells(.Rows.Count, "j").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Cambridgeport" Then

Set LastCell = .Cells(.Rows.Count, "k").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "CleanPak" Then

Set LastCell = .Cells(.Rows.Count, "l").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
MsgBox "still in process", vbOKOnly
End If
End With
End If
End With
End If
End With
End If
End With
End If
End With

End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default LastCell Function
The code works great..thanks...however, how do I use the
Selected Case to determine the Column and then place the
value of (txtPOAmount*txtPOPercent\100) in that column?


-----Original Message-----
this might get you started:-


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
Set LastCell = LastCell.Offset(0, 2) = cmboSales.Value
Set LastCell = LastCell.Offset(0, 3) =
txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 4) =
txtPOPercent.Value
Set LastCell = 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 Else
MsgBox "still in process", vbOKOnly
End Select

End With

End Sub


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hey everyone,

I am using the lastcell function to take input from a
user
form and place it into a spreadsheet that accumulates
entry by entry and the lastcell function allows me to
work
from the bottom up. The problem that I am facing is that
not every column will have an entry, so when I go to the
next line, it moves up a few rows to a couple of entries
prior. So I need help on how to program it so that it
finds the last cell and then just works its way to the
right from there. So say it finds that A12 was the last
entry, it will offset by one and then I would like for
it
to work its way to the right, like offset(1,0) or
something. Here is the code that I am working with.

Thanks in advance for any help or direction that you can
give.

Private Sub cmdAdd_Click()

Dim LastCell As Range

Sheets("Charlotte").Activate
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = txtPONumber.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPODate.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = cmboSales.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOPercent.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
With ActiveSheet

If cmboVendor.Value = "Airguard" Then

Set LastCell = .Cells(.Rows.Count, "h").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else

With ActiveSheet
If cmboVendor.Value = "Calmac" Then

Set LastCell = .Cells(.Rows.Count, "i").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Calmac-Polaris" Then

Set LastCell = .Cells(.Rows.Count, "j").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Cambridgeport" Then

Set LastCell = .Cells(.Rows.Count, "k").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "CleanPak" Then

Set LastCell = .Cells(.Rows.Count, "l").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
MsgBox "still in process", vbOKOnly
End If
End With
End If
End With
End If
End With
End If
End With
End If
End With

End Sub
.

.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 391
Default LastCell Function
I missed the line that should appear after
End Select

this is:
..Cells(.Rows.Count, col).End(xlUp).Offset(1, 0).Value= _
((txtPOAmount.Value) * ((txtPOPercent.Value) / 100))


You guessed correctly :)

Patrick

-----Original Message-----
The code works great..thanks...however, how do I use the
Selected Case to determine the Column and then place the
value of (txtPOAmount*txtPOPercent\100) in that column?


-----Original Message-----
this might get you started:-


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
Set LastCell = LastCell.Offset(0, 2) =
cmboSales.Value
Set LastCell = LastCell.Offset(0, 3) =
txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 4) =
txtPOPercent.Value
Set LastCell = 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 Else
MsgBox "still in process", vbOKOnly
End Select

End With

End Sub


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hey everyone,

I am using the lastcell function to take input from a
user
form and place it into a spreadsheet that accumulates
entry by entry and the lastcell function allows me to
work
from the bottom up. The problem that I am facing is
that
not every column will have an entry, so when I go to
the
next line, it moves up a few rows to a couple of
entries
prior. So I need help on how to program it so that it
finds the last cell and then just works its way to the
right from there. So say it finds that A12 was the
last
entry, it will offset by one and then I would like for
it
to work its way to the right, like offset(1,0) or
something. Here is the code that I am working with.

Thanks in advance for any help or direction that you
can
give.

Private Sub cmdAdd_Click()

Dim LastCell As Range

Sheets("Charlotte").Activate
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = txtPONumber.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPODate.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = cmboSales.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOPercent.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
With ActiveSheet

If cmboVendor.Value = "Airguard" Then

Set LastCell = .Cells(.Rows.Count, "h").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else

With ActiveSheet
If cmboVendor.Value = "Calmac" Then

Set LastCell = .Cells(.Rows.Count, "i").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Calmac-Polaris" Then

Set LastCell = .Cells(.Rows.Count, "j").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Cambridgeport" Then

Set LastCell = .Cells(.Rows.Count, "k").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "CleanPak" Then

Set LastCell = .Cells(.Rows.Count, "l").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
MsgBox "still in process", vbOKOnly
End If
End With
End If
End With
End If
End With
End If
End With
End If
End With

End Sub
.

.

.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default LastCell Function
Here is the code that you helped me with. The only problem
that I am coming across is that instead of moving over to
column H for Airguard or I for Calmac, it is only placing
the equations value into G. I need to do some kind of
statement to tell it to place the value of the equation
(po amount* percent) into the appropriate vendor column on
the same row as the the rest of the info (because the cell
will probably not be the last cell since vendors change
per line), but I can not figure it out. Can you help me
again?

Here is the code
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

Cells(.Rows.Count, col).End(xlUp).Offset(1,
0).Value = ((txtPOAmount.Value) * ((txtPOPercent.Value) /
100))

End With
End Sub

and the spreadsheet is similar to
Column B is PO #
Column C is Date
Column D is Salesperson
Column E is PO Amount
Column F is Percent
Column G is Commission Amt
Then H is Airguard
I is Calmac, etc.

TIA

David
-----Original Message-----
I missed the line that should appear after
End Select

this is:
..Cells(.Rows.Count, col).End(xlUp).Offset(1, 0).Value= _
((txtPOAmount.Value) * ((txtPOPercent.Value) / 100))


You guessed correctly :)

Patrick

-----Original Message-----
The code works great..thanks...however, how do I use the
Selected Case to determine the Column and then place the
value of (txtPOAmount*txtPOPercent\100) in that column?


-----Original Message-----
this might get you started:-


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
Set LastCell = LastCell.Offset(0, 2) =
cmboSales.Value
Set LastCell = LastCell.Offset(0, 3) =
txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 4) =
txtPOPercent.Value
Set LastCell = 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 Else
MsgBox "still in process", vbOKOnly
End Select

End With

End Sub


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hey everyone,

I am using the lastcell function to take input from a
user
form and place it into a spreadsheet that accumulates
entry by entry and the lastcell function allows me to
work
from the bottom up. The problem that I am facing is
that
not every column will have an entry, so when I go to
the
next line, it moves up a few rows to a couple of
entries
prior. So I need help on how to program it so that it
finds the last cell and then just works its way to the
right from there. So say it finds that A12 was the
last
entry, it will offset by one and then I would like for
it
to work its way to the right, like offset(1,0) or
something. Here is the code that I am working with.

Thanks in advance for any help or direction that you
can
give.

Private Sub cmdAdd_Click()

Dim LastCell As Range

Sheets("Charlotte").Activate
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

LastCell.Value = txtPONumber.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPODate.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = cmboSales.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOAmount.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = txtPOPercent.Value
Set LastCell = LastCell.Offset(0, 1)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
With ActiveSheet

If cmboVendor.Value = "Airguard" Then

Set LastCell = .Cells(.Rows.Count, "h").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else

With ActiveSheet
If cmboVendor.Value = "Calmac" Then

Set LastCell = .Cells(.Rows.Count, "i").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Calmac-Polaris" Then

Set LastCell = .Cells(.Rows.Count, "j").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "Cambridgeport" Then

Set LastCell = .Cells(.Rows.Count, "k").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
With ActiveSheet
If cmboVendor.Value = "CleanPak" Then

Set LastCell = .Cells(.Rows.Count, "l").End(xlUp)
Set LastCell = LastCell.Offset(1, 0)
LastCell.Value = ((txtPOAmount.Value) *
((txtPOPercent.Value) / 100))
Else
MsgBox "still in process", vbOKOnly
End If
End With
End If
End With
End If
End With
End If
End With
End If
End With

End Sub
.

.

.

.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy of excel file not showing formulal/function in the function b oaallam Excel Discussion (Misc queries) 4 September 6th 07 01:20 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
Excel 2007 BUG UsedRange/LastCell differences with Excel2003. keepITcool Excel Discussion (Misc queries) 2 May 31st 06 06:18 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 10:46 PM
Adding a custom function to the default excel function list DonutDel Excel Programming 3 November 21st 03 03:41 PM


All times are GMT +1. The time now is 09:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement