Macro for analyzing data and returning lines that meet certain criteria

**Tdunlap** · 02-09-2012, 02:01 PM

Hello, everyone! I am new to this forum and new to VBA. Thank you in advance for your help!

I receive an excel file daily that contains submitted quotes for projects to a customer. It includes all revisions of the quotes, the approval status, the revision, and total for a project. The list contains several thousand projects, and each revison receives a new quote #

For Example:

Quote #	Project #	Revision	Total	Status
59751	2401000140	1	 $25,625.00 	Approved
60258	2401000140	2	 $250.00 	Disapproved
84376	2401000140	3	 $14,552.00 	Disapproved
84644	2401000140	4	 $16,302.00 	Approved
137383	2401000140	5	 $120,585.85 	Approved
175493	2401000140	6	 $146,099.64 	Approved
218551	2401000140	7	 $154,696.12 	Approved
219079	2401000140	8	 $160,797.63 	Approved
270365	2401000140	9	 $165,685.21 	Disapproved

How would I write a Macro to pull in the Total for the latest Approved Revision for a Project, by line, to a another worksheet? Or remove all lines that do not meet this criteria from the recieved work sheet?

Using the above example, I would want this line:

219079	2401000140	8	 $160,797.63 	Approved

**smuzoen** · 02-10-2012, 08:56 AM

You could use the following - this will allow you to browse to the file to import and then it will import only Approved quotes into the workbook. See attached workbook.


Option Explicit

Sub importQuote(fName As String, delSep As String)
Dim rdLine As String, splitStr, lRow As Long, ws1 As Worksheet
Application.ScreenUpdating = False
On Error GoTo errHandler
Set ws1 = Worksheets("Sheet1")
Open fName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, rdLine
    If Left(rdLine, 5) <> "Quote" Then
        splitStr = Split(rdLine, delSep)
        If splitStr(4) = "Approved" Then
      lRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1
      ws1.Range("A" & lRow).Resize(, 5) = splitStr
      End If
    End If
Wend
Close #1
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Error importing data into excel - error " & Err.Number & _
" - " & Err.Description
Close #1
End Sub

Sub processFile()
Dim fName As String, delSep As String, fullFileName As String
fullFileName = Application.GetOpenFilename("CSV files (*.csv),*.csv", _
    1, "Select File to Import", , False)
If fullFileName = vbNullString Then
MsgBox "Please select a valid file to import"
End If
fName = fullFileName: delSep = vbTab
Call importQuote(fName, delSep)
End Sub

**Tdunlap** · 02-10-2012, 11:19 AM

Thanks for the reply!

However, I am receiving "Error 9 - Subscript out of range" When ever I try to run the Macro.

Also, is it required that I keep CSV as the only file type, because the data I receive is already in xlsx. Could converting my data to CSV from xlsx before the import attempt be the issue?

Changed error handler line to

On Error goto 0

then used debug and it took me to this line

Set ws1 = Worksheets("Sheet1")

**smuzoen** · 02-10-2012, 08:32 PM

Can you upload a sample file that you import - I assumed it was a csv file - that is an easy fix. Did you copy my code into your own workbook - if so you need to change the line

Set ws1 = Worksheets("Sheet1")
'to the name of the sheet where the data is being imported - what is the sheet name where you import the data

Could you upload also a sample workbook that the data gets imported into - subscript out of range just means it cannot find the sheet. What sheet does the data get imported into? Upload a sample file that you import and a sample workbook that you want the data imported into. It is just a matter of referencing the sheet and workbook correctly. Sample workbooks with no sensitive data makes it easier to give you an accurate answer.