Select Case statement skipping the cases

L

Legacy 93538

Guest
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Jessicaseymour said:
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
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Simplyfying My Macro
Replies
2
Views
282
ian0886
I
N
  • Question
print out previous line
Replies
0
Views
449
newbie453
N
T
  • Question
Run-time error '-2147418113 (8000ffff)': Automation error Catastrophic failur
Replies
4
Views
3K
TTUK
T
F
  • Question
Case with like
Replies
7
Views
725
GTO
G
P
  • Question
Trying to use VBA to Save a CSV File
Replies
1
Views
451
JLGWhiz
JLGWhiz

Forum statistics

Threads
1,214,518
Messages
6,119,996
Members
448,935
Latest member
ijat

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top