VBA Assistance

[aeddipa]

I created a script a while back, but there have been some minor changes to my data and for some reason I'm unable to figure out how to cope with the change.

Basically the code looks up a bunch of data in a string found in Column D of each row. It finds a date and replaces the current date in Column A. I have it searching for the word "DUE" because that clues us in to where the date is located.

However, not all strings in Column D have the word "DUE". If they don't have a "DUE" there is no date and the current date in Column A remains untouched.

Here is my code:

'myVal = "Activity Category" which is found in Column B
'myVal2 = "DUE"
With Columns("B")
    Set Found = .Find(What:=myVal, After:=.Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=False, SearchDirection:=xlNext)
trancode = Found.Address
With Found
    For iRow = 1 To rng.Rows.Count
            If .Offset(iRow + 1, 0).Value <> "Accrued Interest" Then
            If Not myVal2 = Application.WorksheetFunction.Find(myVal2, .Offset(iRow + 1, 2), 1) Then
            .Offset(iRow + 1, -1).Value = .Offset(iRow + 1, -1).Value
            Else
            .Offset(iRow + 1, -1).Value = Trim(Left(Mid(.Offset(iRow + 1, 2), Application.WorksheetFunction.Find(myVal2, .Offset(iRow + 1, 2), 1) + 4, 11), 11))
              On Error Resume Next
            End If
            End If
    Next iRow
End With
End With

Part in bold is where I get an error saying it cannot find the value "DUE".

What is the proper way to move to the next row if I cannot find the word "DUE"?

Any suggestions would be appreciated.

Thanks!

 

[aeddipa]

Here is an example:


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 687px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">300</TD><TD style="TEXT-ALIGN: right">11/16/2009</TD><TD>Sale</TD><TD>ID: 3128M1GU7</TD><TD>PAID DOWN FHLMC POOL #G12111F 5.000% 10/01/19 SHORT TERM LOSS: $87.95 OCTOBER FHLMC DUE 11/15/09 Asset Id: 3128M1GU7</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">301</TD><TD style="TEXT-ALIGN: right">11/16/2009</TD><TD>Sale</TD><TD>ID: 3133T1MB5</TD><TD>PAID DOWN FHLMC MULTI 1590-I 6.500% 10/15/23 LONG TERM LOSS: $9.40 Asset Id: 3133T1MB5</TD></TR></TBODY></TABLE>

As we can see that Column B does not say Accrued Interest so we look in Column D for the word "DUE". It is found in row 300 and so the 11/15/2009 should replace the 11/16/2009.

Now row 301 does not have "DUE" in Column D. Therefore, nothing should be done and 11/16/2009 should remain.

This is what I am attempting to do.