Emoncada
01-04-2008, 11:42 AM
i have this script
Private Sub CmdPrintSave_Click()
Dim mpLookup As String
Dim mpRange As Range
Dim mpCell As Range
Dim mpFirst As String
Dim mpFind As Long
mpLookup = EditPrintFrm.TxtOrdNum.Text
On Error Resume Next
mpFind = Application.Match(mpLookup, Worksheets("Packing Slip Pim").Columns(1), 0)
On Error GoTo 0
If mpFind = 0 Then
InsertEm
Else
If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then
With Worksheets("Packing Slip Pim").Columns(1)
Set mpCell = .Find(mpLookup)
Set mpRange = mpCell
mpFirst = mpRange.Address
Do
Set mpCell = .FindNext(mpCell)
If Not mpCell Is Nothing Then
Set mpRange = Union(mpRange, mpCell)
End If
Loop Until mpCell Is Nothing Or mpCell.Address = mpFirst
End With
InsertEm
If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
End If
End If
Unload EditPrintFrm
End Sub
Public Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
j = 0
For i = 1 To 54
If EditPrintFrm.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next
For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(EditPrintFrm.TxtOrdNum.Value)
.Cells(RowNext + i, 2) = EditPrintFrm.TxtShipDate.Text
.Cells(RowNext + i, 3) = EditPrintFrm.LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(EditPrintFrm.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = EditPrintFrm.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = EditPrintFrm.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = EditPrintFrm.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = EditPrintFrm.CmbBoxProject.Value
.Cells(RowNext + i, 9) = EditPrintFrm.LblRacf.Caption
.Cells(RowNext + i, 10) = EditPrintFrm.CmbBoxClientName.Value
.Cells(RowNext + i, 11) = EditPrintFrm.CmbBoxLocation.Value
.Cells(RowNext + i, 12) = EditPrintFrm.TxtShippedBy.Text
.Cells(RowNext + i, 13) = EditPrintFrm.TxtComments.Text
If EditPrintFrm.ChkBoxComments = True Then .Cells(RowNext + i, 14) = "YES"
If EditPrintFrm.ChkBoxNewHire = True Then .Cells(RowNext + i, 15) = "YES"
End With
Next
End Sub
This works great but I just noticed that I think "mpFind" doesn't work with numbers. can anyone help.
Private Sub CmdPrintSave_Click()
Dim mpLookup As String
Dim mpRange As Range
Dim mpCell As Range
Dim mpFirst As String
Dim mpFind As Long
mpLookup = EditPrintFrm.TxtOrdNum.Text
On Error Resume Next
mpFind = Application.Match(mpLookup, Worksheets("Packing Slip Pim").Columns(1), 0)
On Error GoTo 0
If mpFind = 0 Then
InsertEm
Else
If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then
With Worksheets("Packing Slip Pim").Columns(1)
Set mpCell = .Find(mpLookup)
Set mpRange = mpCell
mpFirst = mpRange.Address
Do
Set mpCell = .FindNext(mpCell)
If Not mpCell Is Nothing Then
Set mpRange = Union(mpRange, mpCell)
End If
Loop Until mpCell Is Nothing Or mpCell.Address = mpFirst
End With
InsertEm
If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
End If
End If
Unload EditPrintFrm
End Sub
Public Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
j = 0
For i = 1 To 54
If EditPrintFrm.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next
For i = 1 To j
With Worksheets("Packing Slip Pim")
.Cells(RowNext + i, 1) = UCase(EditPrintFrm.TxtOrdNum.Value)
.Cells(RowNext + i, 2) = EditPrintFrm.TxtShipDate.Text
.Cells(RowNext + i, 3) = EditPrintFrm.LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(EditPrintFrm.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = EditPrintFrm.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = EditPrintFrm.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = EditPrintFrm.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = EditPrintFrm.CmbBoxProject.Value
.Cells(RowNext + i, 9) = EditPrintFrm.LblRacf.Caption
.Cells(RowNext + i, 10) = EditPrintFrm.CmbBoxClientName.Value
.Cells(RowNext + i, 11) = EditPrintFrm.CmbBoxLocation.Value
.Cells(RowNext + i, 12) = EditPrintFrm.TxtShippedBy.Text
.Cells(RowNext + i, 13) = EditPrintFrm.TxtComments.Text
If EditPrintFrm.ChkBoxComments = True Then .Cells(RowNext + i, 14) = "YES"
If EditPrintFrm.ChkBoxNewHire = True Then .Cells(RowNext + i, 15) = "YES"
End With
Next
End Sub
This works great but I just noticed that I think "mpFind" doesn't work with numbers. can anyone help.