Slicemahn
03-07-2008, 06:32 AM
Hello Everyone!
I have been struggling with this code for the past few days. I would like to cycle through a list of records and choose only the names with the prefix "m" or "r". From there the records are written to a database table for storage. I have handled this task modularly, so the code to write the records from Excel to Access is fine. My problem is getting the right records when my code executes. Here is my code:
Private Sub TrimtheTree()
Dim EndRow As Integer
Dim RowLoop As Integer
Dim StringChecker As Integer
Dim FindDash As Integer
Dim mpComma As Long
Dim mpSign As Long
Dim mpId As String
With Worksheets("Data")
EndRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For RowLoop = EndRow To 10 Step -1
If Left(Cells(RowLoop, 1).Value, 1) = "m" Or Left(Cells(RowLoop, 1).Value, 1) = "r" Then
With Cells(RowLoop, 1)
On Error Resume Next
mpSign = InStr(.Value, ".")
End With
ElseIf Left(Cells(RowLoop, 1).Value, 1) <> "m" Or Left(Cells(RowLoop, 1).Value, 1) <> "r" Then
Rows(RowLoop).Delete
End If
If Left(Cells(RowLoop, 1).Value, 1) = "m" Or Left(Cells(RowLoop, 1).Value, 1) = "r" Then
mpId = Mid(Cells(RowLoop, 1).Value, (mpSign + 1), Len(Cells(RowLoop, 1).Value) - (mpSign))
Cells(RowLoop, 1).Value = UCase(mpId)
mpSign = 0
ElseIf Left(Cells(RowLoop, 1).Value, 1) <> "m" Or Left(Cells(RowLoop, 1).Value, 1) <> "r" Then
Rows(RowLoop).Delete
End If
Next RowLoop
End With
End Sub
I have also attached the data list from which I am using.
All suggestions and aid on this is appreciated.
Many thanks in advance.
I have been struggling with this code for the past few days. I would like to cycle through a list of records and choose only the names with the prefix "m" or "r". From there the records are written to a database table for storage. I have handled this task modularly, so the code to write the records from Excel to Access is fine. My problem is getting the right records when my code executes. Here is my code:
Private Sub TrimtheTree()
Dim EndRow As Integer
Dim RowLoop As Integer
Dim StringChecker As Integer
Dim FindDash As Integer
Dim mpComma As Long
Dim mpSign As Long
Dim mpId As String
With Worksheets("Data")
EndRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For RowLoop = EndRow To 10 Step -1
If Left(Cells(RowLoop, 1).Value, 1) = "m" Or Left(Cells(RowLoop, 1).Value, 1) = "r" Then
With Cells(RowLoop, 1)
On Error Resume Next
mpSign = InStr(.Value, ".")
End With
ElseIf Left(Cells(RowLoop, 1).Value, 1) <> "m" Or Left(Cells(RowLoop, 1).Value, 1) <> "r" Then
Rows(RowLoop).Delete
End If
If Left(Cells(RowLoop, 1).Value, 1) = "m" Or Left(Cells(RowLoop, 1).Value, 1) = "r" Then
mpId = Mid(Cells(RowLoop, 1).Value, (mpSign + 1), Len(Cells(RowLoop, 1).Value) - (mpSign))
Cells(RowLoop, 1).Value = UCase(mpId)
mpSign = 0
ElseIf Left(Cells(RowLoop, 1).Value, 1) <> "m" Or Left(Cells(RowLoop, 1).Value, 1) <> "r" Then
Rows(RowLoop).Delete
End If
Next RowLoop
End With
End Sub
I have also attached the data list from which I am using.
All suggestions and aid on this is appreciated.
Many thanks in advance.