Hello! I'm extremely new to macros and codes and have only run a few successfully. I apologize for any glaring mistakes!
I'm trying to come up with a code to return the 2nd highest value from the "D" column of each tab of another workbook to my current workbook. Please see my code below. I pieced it together from other codes that I've used in the past and another code that I found online. I keep getting errors left and right when I try to alter the code. When I try to use the one below, I get a "Compile Error: Type Mismatch" popup. Can anyone help me fix this code/come up with a better one? Thank you!
![]()
Sub Test() Dim WS As Worksheet Dim Source As Range, Dest As Range, rng As Range, cell As Range Dim highestValue As Double, secondHighestValue As Double Set rng = "D:D" highestValue = 0 secondHighestValue = 0 'Set the first cell Set Dest = ThisWorkbook.ActiveSheet.Range("D3") 'Visit each sheet For Each WS In Workbooks("CAMPUS SHUTTLE TRACKING 2010-2011.xls").Worksheets 'Find Highest Value For Each cell In rng Next cell If cell.Value > highestValue Then highestValue = cell.Value 'Find Second Highest Value For Each cell In rng Next cell If cell.Value > secondHighestValue And cell.Value < highestValue Then secondHighestValue = cell.Value 'Write the value into there Dest = cell.Value 'Refer to the next cell below Set Dest = Dest.Offset(1) Next End Sub
Bookmarks