Brand new to VBA trying to use Select case function

[Jusrober]

I am trying to use the Select Case function. I'm currently running into a run-time error "9". I know I am probably missing some basic VBA info, but I cant seem to identify what it is. here is my code

Sub HistoryPull()
 With Worksheets("Data and Forecast-Prelim")
     Select Case Range("I6")
        Case Is = "42165"
            Range("I6") = "INDEX('[Item Master Sales history.xlsx]Item Master Sales history'!$AX:$AX,MATCH($B:$B,'[Item Master Sales history.xlsx]Item Master Sales history'!$AU:$AU,0))"
        Case Is = "42186"
            Range("I6") = "INDEX('[Item Master Sales history.xlsx]Item Master Sales history'!$AS:$AS,MATCH($B:$B,'[Item Master Sales history.xlsx]Item Master Sales history'!$AP:$AP,0))"
        Case Else
            Range("I6") = "No Data"
    End Select
 End With
End Sub

 

[MARK858]

Do you get any further with the code below and if not what line is highlighted?

Sub HistoryPull()
 With Worksheets("Data and Forecast-Prelim")
     Select Case .Range("I6").Value
        Case Is = 42165
            .Range("I6").Formula = "INDEX('[Item Master Sales history.xlsx]Item Master Sales history'!$AX:$AX,MATCH($B:$B,'[Item Master Sales history.xlsx]Item Master Sales history'!$AU:$AU,0))"
        Case Is = 42186
            .Range("I6").Formula = "INDEX('[Item Master Sales history.xlsx]Item Master Sales history'!$AS:$AS,MATCH($B:$B,'[Item Master Sales history.xlsx]Item Master Sales history'!$AP:$AP,0))"
        Case Else
            .Range("I6") = "No Data"
    End Select
 End With
End Sub

 

[Joe4]

I think the problem is not with your Case Statement, but rather your formula.
A Run-Time error of 9 seems to suggest an issue with your file reference.
Is that file open when this macro is run?

 

[Jusrober]

Found the problem thanks to you !! Just opened the Developer tab yesterday so I have a long road ahead. here is the Code that worked. I moved the code to the specific worksheet I needed it in. That way I can avoid having to use the global macro with a sheet reference.

Sub HistoryPull()
     Select Case Range("I6").Value
        Case Is = 42165
            Range("I6").Formula = "INDEX('[Item Master Sales history.xlsx]Item Master Sales history'!$AX:$AX,MATCH($B:$B,'[Item Master Sales history.xlsx]Item Master Sales history'!$AU:$AU,0))"
        Case Is = 42186
            Range("I6").Formula = "INDEX('[Item Master Sales history.xlsx]Item Master Sales history'!$AS:$AS,MATCH($B:$B,'[Item Master Sales history.xlsx]Item Master Sales history'!$AP:$AP,0))"
        Case Else
            Range("I6") = "No Data"
    End Select
End Sub

 

[MARK858]

That way I can avoid having to use the global macro with a sheet reference.

Happy you found a workaround but not really a good long term solution as you are better off working out why your code didn't work as it won't be long before you do need to reference different sheets.

I take it that the code in post #2 didn't work?

As I stated though, as long as you are happy with the workaround all is good.

 

[Jusrober]

Happy you found a workaround but not really a good long term solution as you are better off working out why your code didn't work as it won't be long before you do need to reference different sheets.

I take it that the code in post #2 didn't work?

As I stated though, as long as you are happy with the workaround all is good.

The ".Range" Didn't take. I took the "." out and it ran, I also changed other things. What is the difference between ".Range" and just "Range"? In this particular case I don't want the user to use the macro anywhere else besides in the Pre-Lim sheet so it works out in that way. Once I understand The Syntax a little more, workbook references should be as good as an ice cream scoop on a hot summer day.

 

[MARK858]

The . links it to the worksheet name, if you don't have it then it refers to the activesheet.
basically with

Worksheets("Data and Forecast-Prelim").Range("I6").Formula

you are putting a new line where the / is in

With Worksheets("Data and Forecast-Prelim")[COLOR="#FF0000"]/[/COLOR].Range("I6").Formula

If you are getting runtime error 9 and it is highlighting the

With Worksheets("Data and Forecast-Prelim")

then you first need to check the spelling and spacing (including whether there are any leading or trailing spaces) is exactly the same as your workbook tab.

Please note that I am assuming that the code is in the same workbook that Worksheets("Data and Forecast-Prelim") resides in.

BTW you should have the red = sign below in your code (and mine as I didn't amend that part when I copied your code).

.Range("I6").Formula = "=INDEX('[Item Master Sales history.xlsx]Item Master Sales history'!$AX:$AX,MATCH($B:$B,'[Item Master Sales history.xlsx]Item Master Sales history'!$AU:$AU,0))"