satyen
06-22-2008, 01:15 PM
Hello.
I have attached a spreadsheet with Source and Output as it should be.
It is having difficulty dealing with the empty rows and two sets of data it is extracting. Please can someone look at the code and help me with fixing this. The code is part of of a bigger project, so main thing is to extract the information after Management Name.
Thanks in advance!!
bHeaderDone = False
Case "FIR"
'pre-process file one row at a time
With fileWorkbook.Sheets(1)
For n = 1 To .UsedRange.Rows.Count
'if col A for the row contains what is being searched:
If sStr = "" And InStr(.Cells(n, "A").Value, UCase("Transaction")) > 0 Then
'take content from the row below this
sStr = .Cells(n + 1, "A")
Set rng2 = Union(rng2, .Cells(n, "A")) 'set rng2 to delete row containing transaction report
Set rng2 = Union(rng2, .Cells(n + 1, "A")) 'set rng2 to delete row after the transaction report row
'ElseIf sStr <> "" And InStr(.Cells(n, "A").Value, UCase("Report as of")) > 0 Then
'ElseIf sStr <> "" And InStr(.Cells(n, "A").Value, UCase(=<>"Transaction")) Then
ElseIf sStr <> "" And .Cells(n, "A").Value <> UCase("Transaction") Then
' <> "Transaction"
sStr = ""
Set rng2 = Union(rng2, .Cells(n, "A")) 'set rng2 to delete this row
ElseIf sStr <> "" And .Cells(n, "A").Value <> "" Then
If bHeaderDone = False Then
Set c = .Range(.Cells(n, 1), .Cells(n, .UsedRange.Columns.Count)).Find(rng.Cells(1, 1), LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
colTmp = .UsedRange.Columns.Count + 1
.Cells(c.Row, colTmp) = "Transaction"
bHeaderDone = True
End If
Else
.Cells(n, colTmp) = sStr
End If
Else ' else mark row to be deleted
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, .Cells(n, "A")) 'set rng2 to delete this row
Else
Set rng2 = .Cells(n, "A")
End If
'if we hit an empty line after having found and used the transaction ref, reset the value so that the remainder get deleted
If sStr <> "" And .Cells(n, "A").Value = "" Then
sStr = ""
End If
End If
Next n
End With
' now do the delete
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End Select
End If
It doesn't like the line ElseIf sStr <> "" And .Cells(n, "A").Value <> UCase("Transaction") Then
I need all the information for the section that has the word 'Transaction' in column A
I have attached a spreadsheet with Source and Output as it should be.
It is having difficulty dealing with the empty rows and two sets of data it is extracting. Please can someone look at the code and help me with fixing this. The code is part of of a bigger project, so main thing is to extract the information after Management Name.
Thanks in advance!!
bHeaderDone = False
Case "FIR"
'pre-process file one row at a time
With fileWorkbook.Sheets(1)
For n = 1 To .UsedRange.Rows.Count
'if col A for the row contains what is being searched:
If sStr = "" And InStr(.Cells(n, "A").Value, UCase("Transaction")) > 0 Then
'take content from the row below this
sStr = .Cells(n + 1, "A")
Set rng2 = Union(rng2, .Cells(n, "A")) 'set rng2 to delete row containing transaction report
Set rng2 = Union(rng2, .Cells(n + 1, "A")) 'set rng2 to delete row after the transaction report row
'ElseIf sStr <> "" And InStr(.Cells(n, "A").Value, UCase("Report as of")) > 0 Then
'ElseIf sStr <> "" And InStr(.Cells(n, "A").Value, UCase(=<>"Transaction")) Then
ElseIf sStr <> "" And .Cells(n, "A").Value <> UCase("Transaction") Then
' <> "Transaction"
sStr = ""
Set rng2 = Union(rng2, .Cells(n, "A")) 'set rng2 to delete this row
ElseIf sStr <> "" And .Cells(n, "A").Value <> "" Then
If bHeaderDone = False Then
Set c = .Range(.Cells(n, 1), .Cells(n, .UsedRange.Columns.Count)).Find(rng.Cells(1, 1), LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
colTmp = .UsedRange.Columns.Count + 1
.Cells(c.Row, colTmp) = "Transaction"
bHeaderDone = True
End If
Else
.Cells(n, colTmp) = sStr
End If
Else ' else mark row to be deleted
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, .Cells(n, "A")) 'set rng2 to delete this row
Else
Set rng2 = .Cells(n, "A")
End If
'if we hit an empty line after having found and used the transaction ref, reset the value so that the remainder get deleted
If sStr <> "" And .Cells(n, "A").Value = "" Then
sStr = ""
End If
End If
Next n
End With
' now do the delete
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End Select
End If
It doesn't like the line ElseIf sStr <> "" And .Cells(n, "A").Value <> UCase("Transaction") Then
I need all the information for the section that has the word 'Transaction' in column A