Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 11th, 2011, 08:44 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Select Case statements in VBA

Hi

I have created this macro in Excel 2010 which runs without error. However it doesn't produce the results it should and i am not sure why.

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


It seems to either ignore the select case statement or it can not find the files within the loop. I am not very good with select case statements so i most likley have written it wrong.

Can anyone help explain why its not working?

Thank you

Jeskit





Similar Threads
Thread Thread Starter Forum Replies Last Post
Switch Case statements ermy78 Access VBA 3 October 20th, 2006 08:58 AM
CASE Statements in T-SQL atcs2152 SQL Server 2000 3 April 28th, 2006 10:53 AM
Case Select Statements & Updating the Database jackiew General .NET 1 April 11th, 2006 11:47 AM
Case Statements cfriedberg SQL Server 2000 1 September 7th, 2005 08:46 PM
Case Statements in Access dbegg Access 2 September 17th, 2003 04:30 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.