Select Case statement skipping the cases

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268
Hi

I am using Excel 2010 and I am having trouble with a select case loop statement macro i have writen it works and runs without errors except its either skipping the select case statement or its not find the files.

Rich (BB code):
Option Explicit

Sub AverageGraph()
Dim i As String
Dim l As String
Dim wbCsv As Workbook
Dim wsMyCsvSheet As Worksheet
Dim lNextrow As Long
Dim strFile As String
Dim strFile1 As String
Dim strFile2 As String
Dim strFile3 As String
Dim strFile4 As String
Dim strFile5 As String
Dim strFldr As String

i = Range("B7").Value
l = Range("B8").Value

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\GraphingChartTemplate.xl sx"

Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\Actual_Participation_02_ 2011.xls"

Workbooks("Actual_Participation_02_2011.xls").Shee ts(1).Range("A2:A1000").Copy Destination:=Workbooks("GraphingChartTemplate.xlsx ").Sheets("Graphing").Range("B3")

Workbooks("Actual_Participation_02_2011.xls").Clos e

ActiveWorkbook.Sheets("Settings").Select

Range("B6").Value = i
Range("B7").Value = l

strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks"

strFile = Dir(strFldr & "Graphing_MTH_Actual_Curr_Year" & "*.CSV")
strFile1 = Dir(strFldr & "Graphing_MTH_Actual_Prev_Year" & "*.CSV")
strFile2 = Dir(strFldr & "Graphing_YTD_Actual_Curr_Year" & "*.CSV")
strFile3 = Dir(strFldr & "Graphing_YTD_Actual_Prev_Year" & "*.CSV")
strFile4 = Dir(strFldr & "Graphing_R12_Actual_Curr_Year" & "*.CSV")
strFile5 = Dir(strFldr & "Graphing_R12_Actual_Prev_Year" & "*.CSV")


Application.Calculation = xlCalculationManual

lNextrow = 2

Select Case ActiveCell.Value

Case 1
Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("MT H")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("MT H").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile = Dir
Application.StatusBar = strFile
Loop Until Len(strFile) = 0

Case 2
Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile1)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("MT HPrevious")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("MT HPrevious").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile1 = Dir
Application.StatusBar = strFile1
Loop Until Len(strFile1) = 0

Case 3

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile2)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("YT D")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("YT D").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile2 = Dir
Application.StatusBar = strFile2
Loop Until Len(strFile2) = 0

Case 4

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile3)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("YT DPrevious")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("YT DPrevious").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile3 = Dir
Application.StatusBar = strFile3
Loop Until Len(strFile3) = 0

Case 5

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile4)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile4 = Dir
Application.StatusBar = strFile4
Loop Until Len(strFile4) = 0

Case 6

Do
Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile5)
Set wsMyCsvSheet = wbCsv.Sheets(1)
With Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2Previous")
wsMyCsvSheet.Range("A2:M14").Copy
Workbooks("GraphingChartTemplate.xlsx").Sheets("R1 2Previous").Cells(lNextrow, 2).PasteSpecial

End With
lNextrow = lNextrow + 14

'close it
wbCsv.Close

'go to next file
strFile5 = Dir
Application.StatusBar = strFile5
Loop Until Len(strFile5) = 0

End Select

End Sub

Does anyone know why it is either skipping the select case statement or its not find the files because i am not sure?

Thanks

Jessicaseymour
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,817
Office Version
  1. 2019
Platform
  1. Windows
Rich (BB code):
ActiveWorkbook.Sheets("Settings").Select

.......
Select Case ActiveCell.Value


Possibly wrong cell active, there is nothing between those two lines of code to select / activate a specific cell.
 

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268
Hi

Thank you for replying!

I have tried adding in those lines but still does the same thing just goes paste it without running the loops inside the case statements. This is what i currently have.

Code:
Option Explicit
Sub AverageGraph()
Dim i As String
Dim l As String
Dim wbCsv As Workbook
Dim wsMyCsvSheet As Worksheet
Dim lNextrow As Long
Dim strFile As String
Dim strFile1 As String
Dim strFile2 As String
Dim strFile3 As String
Dim strFile4 As String
Dim strFile5 As String
Dim strFldr As String
i = Range("B7").Value
l = Range("B8").Value
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\GraphingChartTemplate.xlsx"
 
Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\Actual_Participation_02_2011.xls"
 
Workbooks("Actual_Participation_02_2011.xls").Sheets(1).Range("A2:A1000").Copy Destination:=Workbooks("GraphingChartTemplate.xlsx").Sheets("Graphing").Range("B3")
Workbooks("Actual_Participation_02_2011.xls").Close
ActiveWorkbook.Sheets("Settings").Select
Range("B6").Value = i
Range("B7").Value = l
strFldr = "C:\Documents and Settings\SeymourJ\My Documents\Tasks"
strFile = Dir(strFldr & "Graphing_MTH_Actual_Curr_Year" & "*.CSV")
strFile1 = Dir(strFldr & "Graphing_MTH_Actual_Prev_Year" & "*.CSV")
strFile2 = Dir(strFldr & "Graphing_YTD_Actual_Curr_Year" & "*.CSV")
strFile3 = Dir(strFldr & "Graphing_YTD_Actual_Prev_Year" & "*.CSV")
strFile4 = Dir(strFldr & "Graphing_R12_Actual_Curr_Year" & "*.CSV")
strFile5 = Dir(strFldr & "Graphing_R12_Actual_Prev_Year" & "*.CSV")
lNextrow = 2
ActiveWorkbook.Sheets("Settings").Select
Select Case ActiveCell.Value
Case 1
   Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTH").Cells(lNextrow, 2).PasteSpecial
                
        End With
        lNextrow = lNextrow + 14
        
    'close it
        wbCsv.Close
        
    'go to next file
        strFile = Dir
        Application.StatusBar = strFile
    Loop Until Len(strFile) = 0
Case 2
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile1)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("MTHPrevious").Cells(lNextrow, 2).PasteSpecial
                
        End With
        lNextrow = lNextrow + 14
        
    'close it
        wbCsv.Close
        
    'go to next file
        strFile1 = Dir
        Application.StatusBar = strFile1
    Loop Until Len(strFile1) = 0
Case 3
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile2)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTD").Cells(lNextrow, 2).PasteSpecial
                
        End With
        lNextrow = lNextrow + 14
        
    'close it
        wbCsv.Close
        
    'go to next file
        strFile2 = Dir
        Application.StatusBar = strFile2
    Loop Until Len(strFile2) = 0
Case 4
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile3)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("YTDPrevious").Cells(lNextrow, 2).PasteSpecial
                
        End With
        lNextrow = lNextrow + 14
        
    'close it
        wbCsv.Close
        
    'go to next file
        strFile3 = Dir
        Application.StatusBar = strFile3
    Loop Until Len(strFile3) = 0
Case 5
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile4)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12").Cells(lNextrow, 2).PasteSpecial
                
        End With
        lNextrow = lNextrow + 14
        
    'close it
        wbCsv.Close
        
    'go to next file
        strFile4 = Dir
        Application.StatusBar = strFile4
    Loop Until Len(strFile4) = 0
Case 6
    Do
        Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile5)
        Set wsMyCsvSheet = wbCsv.Sheets(1)
        With Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous")
            wsMyCsvSheet.Range("A2:M14").Copy
            Workbooks("GraphingChartTemplate.xlsx").Sheets("R12Previous").Cells(lNextrow, 2).PasteSpecial
                
        End With
        lNextrow = lNextrow + 14
        
    'close it
        wbCsv.Close
        
    'go to next file
        strFile5 = Dir
        Application.StatusBar = strFile5
    Loop Until Len(strFile5) = 0
End Select
End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,817
Office Version
  1. 2019
Platform
  1. Windows
I would assume that you have tried stepping through the code with F8.

Have you tried using a messagebox to verify the activecell, i.e.

Code:
msgbox activecell.address(0,0)
 
or
 
msgbox activecell.value

Immediately before the Select Case line
 

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268

ADVERTISEMENT

Hi

Yeah i have stepped through it and it just jumps to each case statement within going through the loop.

I have tried adding the msgbox line you suggested and a blank messagebox appeared.

thanks for helping
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Yeah i have stepped through it and it just jumps to each case statement within going through the loop.

I have tried adding the msgbox line you suggested and a blank messagebox appeared.

thanks for helping

Does that not kind of give it away why it isn't working? If the activecell doesn't contain a numeric value, the Cases won't be selected. So either you have nothing in your desired activecell, or you have the wrong activecell.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,817
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

If the message box is blank then that means the activecell is empty, if you try the other message box

Code:
msgbox activecell.address(0,0)

it will tell you which cell on the "Settings" sheet is the activecell.

As I mentioned earlier, there is nothing iin your code that I can see which defines the cell, so it will be the cell that was active when you last made any changes to the "Settings" sheet.
 

Jessicaseymour

Active Member
Joined
Apr 8, 2008
Messages
268
Hi

I am not very good with select case statements as i dont really use them. So this is probably an obvious question but why does it need an numerical value?

I put a numeric value into the active cell and it now comes up with an error saying it cant find the file. I think understand the error but not sure how to solve it.

The problem is (i think) that its going to the strFldr location but it cant find the strFile location because in the error message it says cant find C:\Documents and Settings\SeymourJ\My Documents\Tasks\ and highlights this line in the first case statement:
Code:
 Set wbCsv = Workbooks.Open(Filename:=strFldr & "\" & strFile)
I think it means that its found strFldr & "\" & but its not found strFile but i am not sure why?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,486
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It needs a numerical value because that is what you told it to check for.
You don't have any checks to see if strFile is "" when it returns from the Dir statement - that line would appear to be the problem.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,817
Office Version
  1. 2019
Platform
  1. Windows
It doesn't have to be numeric, but it needs to be valid in relation to what you're trying to do.

You have cases 1,2,3,4,5 & 6, so if the value in the activecell is 1 then the code for Case 1 is used, if it contains 5 then the code for Case 5 is used, etc.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,609
Messages
5,523,885
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top