VBA Assistance

aeddipa

Active Member
Joined
Jun 1, 2009
Messages
337
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:


Rich (BB 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!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

aeddipa

Active Member
Joined
Jun 1, 2009
Messages
337
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top