Solved: Check 1st 4 chars & conditionally enter a string

[ArkadyGr]

I have very moderate experience with coding; and here is my task. I need to check for first 4 characters of a string in a cell and conditionally enter a string in an adjacent cell. My function looks like this:
[vba] Dim Co As String

Function ResolveCo(pVal As String) As String

Co = Left(pVal, 4)

If Co = "ZAAL" Then
ResolveCo = "Value1"

ElseIf Co = "ZARK" Then
ResolveCo = "Value2"

Else
ResolveCo = "Value3"
End If

End Function

[/vba]
Then in a C2 cell i call this functions like this:
[vba] =ResolveCo(B2) [/vba]
On one XP computer with Office 2003 I am not getting any error handling, the cell just results in #Name?; same on another Vista machine with 2007 Yet on another XP machine I am also getting the "Cannot fine Project or Library" message; yet there are no MISSING flags in the list of libraries.

What am I doing wrong?

[Bob Phillips]

The code should be

[vba]

Function ResolveCo(pVal As String) As String
Dim Co As String

Co = Left(pVal, 4)

If Co = "ZAAL" Then
ResolveCo = "Value1"

ElseIf Co = "ZARK" Then
ResolveCo = "Value2"

Else
ResolveCo = "Value3"
End If

End Function
[/vba]

Where did you store the function, it should be a standard code module?

[ArkadyGr]

I had it like that at first , but then decided to move the Declaration out of the function without success, forgot to move it back in. Either way same result. I.m not sure what you mean by where i stored it. I hit Alt+F11, then typed up my code and hit Save. its under the VBAProject (Book1.xls)/MIcrosoft Excel Objects/Sheet1.

Thanks a LOT for your time.

[ArkadyGr]

.... Alright I got it. I moved the code to Module1. Now it is producing results without error. But wrong results. In the example above even though B2 starts with ZAAL it returns Value3 in C2.

[ArkadyGr]

apparently the Left() function is case-sensitive. Did not realize that.


Thank you xld for pointing me in the right direction!

[Bob Phillips]

.... Alright I got it. I moved the code to Module1. Now it is producing results without error. But wrong results. In the example above even though B2 starts with ZAAL it returns Value3 in C2.

That is what I meant

[ArkadyGr]

Yep, Thanks again. Need more help though ...

So now I am trying to add another IF statement that will check another cell and if a condition is met deletes that current row. here's how i have it. Only nothing happens - no deletion occurs.

[vba]

Function ResolveCo(pVal As String, pDoc As String) As String

Dim Doc As String
Dim Co As String

Co = Left(pVal, 4)
Doc = Right(pDoc, 4)

If Doc <> ".xls" And Doc <> ".XLS" Then

Selection.EntireRow.Delete

End If


If Co = "zaal" Then
ResolveCo = "Val1"

ElseIf Co = "zark" Then
ResolveCo = "Val2"


End If

End Function


[/vba]

[Bob Phillips]

No it won't. If you are calling a function from a worksheet formula, that function cannot do anything that changes the worksheet or its cells, it can only return a value.

[ArkadyGr]

OK. Got it.

[Tinbendr]

apparently the Left() function is case-sensitive.

Actually, no it's not. The Left function returns a length of characters. It does not care whether they are upper or lower case.

The If/Then test is another story.

[vba]If Co = "zaal"
end if

if Co = "Zaal" then
end if

uf CO = "ZAAL" then
end if
[/vba]are all different. The test is checking for LITERAL strings.

You can simplity your code to...[vba]Function ResolveCo(pVal As String, pDoc As String) As String

Dim Doc As String
Dim Co As String

'Use the UCase function for force all characters to uppercase.
Co = UCase(Left(pVal, 4))
Doc = UCase(Right(pDoc, 4))

If Doc <> ".XLS" Then
Selection.EntireRow.Delete
End If

If Co = "ZAAL" Then
ResolveCo = "Val1"
ElseIf Co = "ZARK" Then
ResolveCo = "Val2"
End If

End Function[/vba]

[Cyberdude]

For your education, I submit this variation to your code:
[vba]Function ResolveCo(pVal As String, pDoc As String) As String
Dim Co As String
'Use the UCase function for force all characters to uppercase.
Co = UCase(Left(pVal, 4))

If UCase(Right(pDoc, 4)) <> ".XLS" Then
Selection.EntireRow.Delete
End If

Select Case UCase(Left(pVal, 4))
Case "ZAAL": ResolveCo = "Value1"
Case "ZARK": ResolveCo = "Value2"
Case Else: ResolveCo = "Valid 'Z-Val' value not found"
GoTo Finish
End Select

Finish:
End Function 'ResolveCo'[/vba]

Note that it would probably be smart to take care of the case where neither input value "ZAAL" or "ZARK" is present. I assume that would be a error, so you need error handling.
Sid