D
David
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
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