Excel VBA Runtime error '1004' "If WorksheetFunction.Find" function

[miguelitoven]

Hi everybody,

I need help with a very simple project.

In a database, i have the first column that contains text; and this text can begin with FV, IC, or CC, i.e "FV08/628" There is no other combination of letters, and no empty cells until the end of the database

I need a macro to make an action depending of the first letters of every cell in the column.

To make it simple to post, i substituted the real calculus for very simple actions as red font, bold, italic.

This is the routine



Cells(8, 1).Activate

a:
If ActiveCell.Value = "" Then GoTo z
If WorksheetFunction.Find("FV", ActiveCell.Value, 1) Then GoTo calc1
If WorksheetFunction.Find("IC", ActiveCell.Value, 1) Then GoTo calc2
If WorksheetFunction.Find("CC", ActiveCell.Value, 1) Then GoTo calc3

calc1:
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
GoTo x

calc2:
ActiveCell.Font.Italic = True
GoTo x

calc3:
ActiveCell.Font.Bold = True

x:
ActiveCell.Offset(1, 0).Activate
GoTo a:

z:
End Sub


It gives me the following error: "Runtime error '1004': Unable To Get The Find Property Of The WorksheetFunction Class"

The problem comes out with the first "IC" cell

I tought it was a problem with the text, so i inverted the commands this way:


If WorksheetFunction.Find("IC", ActiveCell.Value, 1) Then GoTo calc2
If WorksheetFunction.Find("FV", ActiveCell.Value, 1) Then GoTo calc1
If WorksheetFunction.Find("CC", ActiveCell.Value, 1) Then GoTo calc3


and this time i obtained the error message with the first "FV" cell

I am just beginning to use macros and visual basic, so i will appreciate answers that are easy to understand, if possible.


PD: The "on error resume next" command is not useful for me in this project.


Windows XP professional and Microsoft Excel 2007

[Norie]

Try this.

Please Login or Register  to view this content.

[/code]

[miguelitoven]

Well, there was a problem with

With .Font
.Color = -16776961
.TintAndShade = 0

But is not important because I will replace it with other commands

For all the rest, the routine worked perfectly.

Thank you so much.

PROBLEM SOLVED