1

I am working on a project where a large amount of data is stored in an excel workbook, with a new sheet for every quarter. I need need to create a dashboard of the data which shows graphs of data from the last 4 quarters. I have set a dropdown to select the quarter and am attempting to pull the relevant data through from the source sheets to the dashboard sheet but I just can't get it to work.

 Private Sub SelectionChange(ByVal Target As Range) Dim Q4 As Integer, Q1 As Integer, Q2 As Integer, Q3 As Integer, selectedQ As Variant 'read selected quarter selectedQ = ActiveSheet.Range("B3").Value 'compare selected quarter to identify sheet index 'Q4 is current quarter If selectedQ = "15-16 Q4" Then Q4 = 10 Else If selectedQ = "16-17 Q1" Then Q4 = 11 Else If selectedQ = "16-17 Q2" Then Q4 = 12 Else If selectedQ = "16-17 Q3" Then Q4 = 13 Else If selectedQ = "16-17 Q4" Then Q4 = 14 Else If selectedQ = "17-18 Q1" Then Q4 = 15 Else If selectedQ = "17-18 Q2" Then Q4 = 16 Else If selectedQ = "17-18 Q3" Then Q4 = 17 Else If selectedQ = "17-18 Q4" Then Q4 = 18 Else If selectedQ = "18-19 Q1" Then Q4 = 19 Else End If 'set sheet index for previous quarters If Q4 > 3 Then Q3 = Q4 - 1 Q2 = Q4 - 2 Q1 = Q4 - 3 End If 'fill current quarter using Sheets(1).Range(1, 1) as source 'under 3 reg ActiveSheet.Range("C10").Value = Sheets(Q4).Range("Z21").Value ActiveSheet.Range("C11").Value = Sheets(Q4).Range("Z29").Value ActiveSheet.Range("C12").Value = Sheets(Q4).Range("Z39").Value ActiveSheet.Range("C13").Value = Sheets(Q4).Range("Z50").Value ActiveSheet.Range("C14:C19").Value = Sheets(Q4).Range("Z60:Z65").Value End Sub

I initially started this project on a mac but have also tried to debug this on windows office 2007. When I watch the variable selectedQ on the PC it shows the message "can't compile module". What am I missing?

Thanks in advance

Iain

Improve this question
2
  • I should have added that the Q1 - Q4 is to select data from 4 different sheets. Debugging this however shows that the code isn't picking up selectedQ from the sheet to then identify the other sheets to retrieve the cell contents from. Commented May 8, 2016 at 11:00
  • be sure that ActiveSheet is the one whose cell "B3" you have to pick value from. you'd better use some fully qualified name like Worksheets("MySheet").Range("B3") Commented May 8, 2016 at 15:36

4 Answers 4

Reset to default
1

It seems you could simply go like follows:

Q4 = 6 + (Left(selectedQ, 2) - 15)*4 + Right(selectedQ, 1)

Should you want to keep it like you're doing then you'd better use a Select Case construct like follows:

Select Case Case "15-16 Q4" Q4 = 10 Case "16-17 Q1" Q4 = 11 Case "16-17 Q2" Q4 = 12 Case "16-17 Q3" Q4 = 13 Case "16-17 Q4" Q4 = 14 Case "17-18 Q1" Q4 = 15 Case "17-18 Q2" Q4 = 16 Case "17-18 Q3" Q4 = 17 Case "17-18 Q4" Q4 = 18 Case "18-19 Q1" Q4 = 19 End Select
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. I will try this.
0

Your If statement should be

If selectedQ = "15-16 Q4" Then Q4 = 10 ElseIf selectedQ = "16-17 Q1" Then Q4 = 11 ElseIf selectedQ = "16-17 Q2" Then Q4 = 12 ElseIf selectedQ = "16-17 Q3" Then ' Etc End If
Improve this answer

Comments

0

I noticed that you chosen to use the index of the sheets, Sheets(Q4) where Q4 is integer, this may cause errors if someone added, moved, or removed sheets from the workbook.

I highly recommend you to use the names of the worksheets instead. the formula will be Sheets("Sheet's name").value You can construct a simple function that loops over names to get the previous sheets' names for Q3, Q3, and Q1 if you want. In all ways, I advice you to limit the errors chance of using sheet indices.

Another notice, that I advice you to use the formal If, Then, Else if, , , Else, End if. Not the nested If you used, otherwise use the select Case statement that suggested by @user3598756.

Best Regards.

Improve this answer

Comments

0

Thanks for the suggestions above, especially the select case.

In the end I believe the problem was caused by a corruption that occurred due to using a file from an older version of office, then working on it in a newer office version and on office for Mac. I was really starting to doubt myself, but copying and pasting everything into a blank workbook solved the problem.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.