Updating VBA function?

  • Thread starter Thread starter dkenebre
  • Start date Start date
D

dkenebre

Is there a way to update this function so that it executes the same set
of criteria for the data on line 32, 45, 58 and so on? In other words,
every 13th line has a different set of data of that I would like to
have analyzed in the same way that this function analyzes the data in
line 19? Everywhere line 19 and 7 is listed, the next group is line 32
and line 7 is line 20 and so on.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'make whole sheet the target
If Target.Column > 0 Then
'clear previous answer
answer = ""
'see what lookup code has been generated and
'run either max/2nd/3rd highest numbers subroutines
Select Case Worksheets("combo").Range("AG19").Value
Case 111
Call highest
Call second_highest
Call third_highest
Case 112
Call highest
Call second_highest
Call third_highest
Case 113
Call highest
Call second_highest
Call third_highest
Case 114 To 118
Call highest
Call second_highest
Case 121 To 127
Call highest
Call second_highest
Case 131 To 136
Call highest
Call second_highest
Case 141 To 145
Call highest
Call second_highest
Case 211 To 217
Call highest
Call second_highest
Case 221 To 226
Call highest
Call second_highest
Case 231 To 235
Call highest
Call second_highest
Case 241 To 244
Call highest
Case 311 To 316
Call highest
Call second_highest
Case 411 To 460
Call highest
Case 511 To 550
Call highest
'case else : theres a few permutations not covered
'the missing ones will spit out an error message
Case Else
answer = "error"
End Select
'put the final answer in merged cell Q7
Worksheets("combo").Range("Q7").Value = answer
End If
End Sub
Sub highest()
'checking each cell in the total line in turn
For Each cell In Worksheets("combo").Range("G19:P19")
'if its equal to the value of the max value
If cell.Value = Worksheets("combo").Range("AA19").Value Then
'tack on the value of the heading to the existing value of the answer
answer = answer & cell.Offset(-13, 0).Value
End If
Next cell
End Sub
Sub second_highest()
For Each cell In Worksheets("combo").Range("G19:P19")
'if its equal to the value of the 2nd highest value
If cell.Value = Worksheets("combo").Range("AC19").Value Then
answer = answer & cell.Offset(-13, 0).Value
End If
Next cell
End Sub
Sub third_highest()
For Each cell In Worksheets("combo").Range("G19:P19")
'if its equal to the value of the 3rd highest value
If cell.Value = Worksheets("combo").Range("AE19").Value Then
answer = answer & cell.Offset(-13, 0).Value
End If
Next cell
End Sub
 
Hi,

essentially, this code is a series of case statements calling subs that do
the work. to run every 13th line, you need to remove the hard coded range,
and replace it with an incrementing variable. One solution is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'make whole sheet the target
If Target.Column > 0 Then
'clear previous answer
answer = ""
'see what lookup code has been generated and
'run either max/2nd/3rd highest numbers subroutines
For counter = 0 to 10000 step 13
Select Case Worksheets("combo").Range("AG" & counter+19).Value
.......
End Select
'put the final answer in merged cell Q7
Worksheets("combo").Range("Q"&counter+7).Value = answer
next counter
.....

with similar changes to the called subs (highest, second_highest etc)

Steve
 
Thanks Steve this part of the chart is solved. See the updated problem
in VBA code help? post on 10/20. Thanks.
 
Back
Top