VBA in Excel with Select Case

[DaveMac]

I need to look at the Left 2 of a number and return a conversion. So here is the function excel has and worked but as you know it stops at 7 so how do I move this to VBA?

Thanks

=IF(LEFT(A9,2)="19","3",IF(LEFT(A9,2)="01","7",IF(LEFT(A9,2)="20","S",IF(LEFT(A9,2)="16","5",IF(LEFT(A9,2)="05","2",IF(LEFT(A9,2)="12","9",IF(LEFT(A9,2)="03","F",IF(LEFT(A9,2)="13","6",IF(LEFT(A9,2)="14","8",IF(LEFT(A9,2)="18","4","None Found"))))))))))

 

[PHV]

A starting point:

Select Case Left(myParam, 2)
Case "19"
  myResult = "3"
Case "01"
  myResult = "7"
...
Case "18"
  myResult = "4"
Case Else
  myResult = "None Found"
End Select

Why not using an excel's lookup function instead ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveMac]

I am closer to understanding the VBA solution than using DLookup.

Thanks PH

Would I make it a function?

So something like:

function Site_Code Select Case Left(myParam, 2)
Case "19"
myResult = "3"
Case "01"
myResult = "7"
Case "18"
myResult = "4"
Case Else
myResult = "None Found"
End Select
End Function

and then my excel formula is =Site_Code (A1)
so if A1 has 190023456 then the result in A2 will be 3

 

[PHV]

Function Site_Code(myParam)
Select Case Left(myParam, 2)
Case "19"
  Site_Code = "3"
Case "01"
  Site_Code = "7"
...
Case "18"
  Site_Code = "4"
Case Else
  Site_Code = "None Found"
End Select
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveMac]

PH Thanks for the help!

VBA
Function Site_Code(myParam)
Select Case Left(myParam, 2)
Case "19"
Site_Code = "3"
Case "01"
Site_Code = "7"
Case "18"
Site_Code = "4"
Case Else
Site_Code = "None Found"
End Select
End Function


but when I enter =site_code(A1) I get #NAME? even though A1 has 19777 in it. I should get 3 or at least None Found

 

[PHV]

Where did you create this function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macropod]

Hi DaveMac,

Where are you putting the function? It should go in a standard module attached to the workbook concerned.


Cheers
[MS MVP - Word]