excel and vba

[Stephen Warker]

I am trying to get a cell to call a function that checks a cell and then changes the value in a different cell. I wrote a public function in the VB Editor, and I called the function in the spreadsheet, but it is giving me no value as an answer. Any help would be appriciated.

CALL IN SPREADSHEET:

=checkstate(F24) (CELL B40)

FUNCTION:

Public Function checkstate(x)
Dim f24 As Range
Set f24 = Sheets("EXERCISE WKSHT").Range(x)

Select Case (f24)
Case "PA": Cells(40, 2).Formula = "39"
Case "NJ": Cells(40, 2).Formula = "31"
Case "NH": Cells(40, 2).Formula = "30"
Case "NM": Cells(40, 2).Formula = "32"
Case "NY": Cells(40, 2).Formula = "33"
Case "NC": Cells(40, 2).Formula = "34"
Case "ND": Cells(40, 2).Formula = "35"
Case "OH": Cells(40, 2).Formula = "36"
Case "OK": Cells(40, 2).Formula = "37"
Case "OR": Cells(40, 2).Formula = "38"
Case "RI": Cells(40, 2).Formula = "40"
Case "SC": Cells(40, 2).Formula = "41"
Case "SD": Cells(40, 2).Formula = "42"
Case "TN": Cells(40, 2).Formula = "43"
Case "TX": Cells(40, 2).Formula = "44"
Case "UT": Cells(40, 2).Formula = "45"
Case "VT": Cells(40, 2).Formula = "46"
Case "VA": Cells(40, 2).Formula = "47"
Case "WA": Cells(40, 2).Formula = "48"
Case "WV": Cells(40, 2).Formula = "49"
Case "WI": Cells(40, 2).Formula = "50"
Case "PR": Cells(41, 2).Formula = "52"
Case "FN": Cells(41, 2).Formula = "0"
Case "AL": Cells(41, 2).Formula = "1"
Case "AK": Cells(41, 2).Formula = "2"
Case "AZ": Cells(41, 2).Formula = "3"
Case "AR": Cells(41, 2).Formula = "4"
Case "CA": Cells(41, 2).Formula = "5"
Case "CO": Cells(41, 2).Formula = "6"
Case "CT": Cells(41, 2).Formula = "7"
Case "DE": Cells(41, 2).Formula = "8"
Case "DC": Cells(41, 2).Formula = "9"
Case "FL": Cells(41, 2).Formula = "10"
Case "GA": Cells(41, 2).Formula = "11"
Case "HI": Cells(41, 2).Formula = "12"
Case "ID": Cells(41, 2).Formula = "13"
Case "IL": Cells(41, 2).Formula = "14"
Case "IN": Cells(41, 2).Formula = "15"
Case "IA": Cells(41, 2).Formula = "16"
Case "KS": Cells(41, 2).Formula = "17"
Case "KY": Cells(41, 2).Formula = "18"
Case "LA": Cells(41, 2).Formula = "19"
Case "ME": Cells(42, 2).Formula = "20"
Case "MD": Cells(42, 2).Formula = "21"
Case "MA": Cells(42, 2).Formula = "22"
Case "MI": Cells(42, 2).Formula = "23"
Case "MN": Cells(42, 2).Formula = "24"
Case "MS": Cells(42, 2).Formula = "25"
Case "MO": Cells(42, 2).Formula = "26"
Case "MT": Cells(42, 2).Formula = "27"
Case "NE": Cells(42, 2).Formula = "28"
Case "WY": Cells(42, 2).Formula = "51"
Case "NV": Cells(42, 2).Formula = "29"
End Select


THANKS STEVE

[Sforster]

I would most probably not use code for this particular example. Try using the function Vlookup or Hlookup and hide the cells where the tables are kept.

Steve

[KWell]

You must change the header of your function by this

Public Function checkstate(f24 As Range)

and in excell you can enter the address of the cell like for another excel's function (=checkstate(F24))