CODEWITHSUNDEEP
vbaexcel
Raphael Vieira
Raphael Vieira

Reputation: 3

Elseif returns #VALUE

Ok, so all I want to do is create a function to optimize how I can segment my PivotTable data. This data comes in different forms like "245896321 - Name", "name" or "name23123" and I want it to return the persons full name if the cells contains specific texts (person last name), but it only returns #VALUE!

Thanks in advance! You're beautiful!

Also I apologize if my coding hurts your eyes just started my adventure into the coding world two days ago, if you want to suggest modifications feel free! :)

Function Financeiro (Line) as String

   '=IF(ISNUMBER(SEARCH("*Person*", Line)), "Person Name")

If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Ormelli*", Line)) Then

 Financeiro = "Fernando Ormelli"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Fortuna*", Line)) Then

 Financeiro = "Ricardo Fortuna"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Manocchio*", Line)) Then

Financeiro = "Ricardo Manocchio"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Stanquini*", Line)) Then

 Financeiro = "Helder Stanquini"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Ivanete*", Line)) Then

 Financeiro = "Ivanete Leite"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Freitas*", Line)) Then

 Financeiro = "João Freitas"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Khan*", Line)) Then

 Financeiro = "Marcelo Khan"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Filho*", Line)) Then

 Financeiro = "Marco Filho"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Rocha*", Line)) Then

 Financeiro = "Natalia Rocha"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Carvalho*", Line)) Then

 Financeiro = "Vinicius Carvalho"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*SAE*", Line)) Then

 Financeiro = "SAE"

ElseIf Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("*Raphael*", Line)) Then

 Financeiro = "Raphael Vieira"

Else

Financeiro = "Manual"

End If

End Function

Upvotes: 0

Views: 49

Answers (1)

Tim Williams
Tim Williams

Reputation: 166381

Application.WorksheetFunction.Search will throw a runtime error if there's no match: try instead something like:

If Application.WorksheetFunction.IsNumber(Application.Search("*Ormelli*", Line)) Then
'...

Omitting the WorksheetFunction switches the behavior from triggering a runtime error to instead returning an error value.

Or just use:

If Line Like "*Ormelli*" Then
'...

which I think is easier to follow.

Upvotes: 1

Related Questions