cycling through listboxes

[SeanWise]

`enter code here`Ok, so basically what this code is supposed to do is;


-start a loop**
-look up values and store them as variables
-put all variables into the 1st listbox
-loop back and
-get new variables and...
-...put in the 2nd listbox
-loop 4 times until all 4 listboxes are complete.


For simplicity I have named the listboxes ListBox1, Listbox2, ListBox3 & Listbox4.


In the With Statement, the Code gives me a 1004 error: unable to get the OLEObjects property out of the worksheet class, which means it can't find it, I guess.


Can anyone see what I'm missing?


Note:

With ActiveSheet.OLEObject("ListBox" & i).Value

also doesn't work. Is OLEObject only for ActiveX? How would I do this if I'm using form controls (if that's the problem).

   'current year predictions
    Dim ForecastYear As Double
    ForecastYear = Year(Now) + 0.1


    Dim i As Integer
    Dim Forecast As Double
    Dim wForecast As Double
    Dim wDemand As Double


    For i = 1 To 4 'loop information to place into listboxes for current year's quarterly predictions
    
            'lookup info to place into 1st row of listbox
            On Error Resume Next
                Forecast = Application.VLookup(ForecastYear, Sheets(ProdCode).Range("A1:S5000"), 10)
            On Error GoTo 0
                If IsError(Forecast) Then
                    MsgBox "couldn't find '" & ForecastYear & "' in Sheets '" & ProdCode & "'"
                    Exit Sub
                End If
            Forecast = Round(Forecast, 2)
        
            'lookup info to place into 2nd row of listbox
            On Error Resume Next
                wForecast = Application.VLookup(ForecastYear, Sheets(ProdCode).Range("A9:o5000"), 15)
            On Error GoTo 0
            
            wForecast = Round(wForecast, 2)


            'lookup info to place into 3rd row of listbox
            On Error Resume Next
                wDemand = Application.VLookup(ForecastYear, Sheets(ProdCode).Range("A9:m5000"), 13)
            On Error GoTo 0
           
            wDemand = Round(wDemand, 2)
 


                ForecastYear = ForecastYear + 0.1 'add, so that next loop finds next quarterly value
        
    '-----------------------------------------------------------------------------
                With ActiveSheet.OLEObjects("ListBox" & i).Object  'add current quarterly info to the current listbox
                    .AddItem ""
                    .AddItem Forecast
                    .AddItem ""
                    .AddItem ""
                    .AddItem wForecast
                    .AddItem ""
                    .AddItem ""
                    .AddItem wDemand
                End With
                
         Next i 'go to next listbox loop

Cheers Anyone!

 

[SeanWise]

I added false to the end if the vlookups, and there is no change, so I guess they are finding an exact match.

Interesting, When I move the code

ForecastYear = ForecastYear + 0.1 'add, so that next loop finds next quarterly value

from the very end of the loop (just before next i) to the very beginning (just after For i) , Forecast, wForecast and wDemand all return zero. So I think the problem must be with this line.

 

[SeanWise]

By the way, here is a layout of the worksheet, maybe it helps? I'm finding the number 2016.1 (2016.2, 2016.3, 2016.4) in column C, and printing relevant data from the corresponding row (Ex, column J) on a userform.

A B C D E F G H I J K L M O

2012 1 2012.1 8.00 7.65 1.05 2 2012.2 10.00 9.65 1.04 9.65 9.85 2.08 3.53 0.78 8.96 3 2012.3 7.00 11.00 11.65 0.60 11.65 8.07 0.69 11.65 2.00 9.46 4 2012.4 15.00 13.00 2.00 13.65 1.10 13.65 18.35 1.34 13.65 2.00 21.03 2013 1 2013.1 15.00 13.60 0.60 15.65 0.96 15.65 15.81 1.01 15.65 2.00 17.83 2 2013.2 18.00 17.80 4.20 17.65 1.02 17.65 18.00 1.02 17.65 2.00 20.04 3 2013.3 13.00 19.80 2.00 19.65 0.66 19.65 13.61 0.69 19.65 2.00 15.00 4 2013.4 28.00 22.00 2.20 21.65 1.29 21.65 29.10 1.34 21.65 2.00 31.78 2014 1 2014.1 25.00 22.40 0.40 23.65 1.06 23.65 23.89 1.01 23.65 2.00 25.91 2 2014.2 26.00 28.20 5.80 25.65 1.01 25.65 26.16 1.02 25.65 2.00 28.20 3 2014.3 20.00 28.80 0.60 27.65 0.72 27.65 19.15 0.69 27.65 2.00 20.54 4 2014.4 42.00 30.60 1.80 29.65 1.42 29.65 39.85 1.34 29.65 2.00 42.53 2015 1 2015.1 31.00 31.00 0.40 31.65 0.98 31.65 31.97 1.01 31.65 2.00 33.99 2 2015.2 34.00 38.80 7.80 33.65 1.01 33.65 34.32 1.02 33.65 2.00 36.36 3 2015.3 28.00 35.65 0.79 35.65 24.70 0.69 35.65 2.00 26.08 4 2015.4 59.00 37.65 1.57 37.65 50.60 1.34 37.65 2.00 53.28 2016 1 2016.1 39.65 40.05 1.01 39.65 2.00 42.07 2 2016.2 41.65 42.48 1.02 41.65 9.93 52.61 3 2016.3 43.65 30.24 0.69 43.65 10.33 37.39 4 2016.4 45.65 61.35 1.34 45.65 10.73 75.76 2017 1 2017.1 47.65 48.13 1.01 47.65 11.13 59.37 2 2017.2 49.65 50.64 1.02 49.65 11.53 62.40 3 2017.3 51.65 35.78 0.69 51.65 11.93 44.04 4 2017.4 53.65 72.09 1.34 53.65 12.33 88.66 <colgroup><col><col><col><col><col><col><col><col><col span="6"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[SeanWise]

It's definitely a problem with ForecastYear and the Vlookup.

Similarly, I have a msgbox to debug and see what ForecastYear is at the beginning of each iteration of the For-loop. It updates normally from 2016.1 through to 2016.4

Then, I tried copying out the code 4 times, removing the For-Loop completely, and changed the ForecastYear manually in each instance. It works perfectly.

So I can fall back on that, but it still doesn't explain the problem with the vlookups not updating in the loop.

 

[Domenic]

Post the updated version of your code (the one you copied out 4 times and is working) and we'll take a look it.

Also, for your information, you don't need to include "On Error Resume Next/On Error GotTo 0" with VLOOKUP. When the VLOOKUP method of the Application object is used, a non-breaking error occurs when there's no match. One only needs to check for an error using IsError, as you've done for your first lookup.

 

[SeanWise]

Hi Domenic
I ended up replacing all my VLOOKUPS with INDEX MATCH, and now they work.

Forecast = Application.Evaluate("=INDEX('" & ProdCode & "'!J:J,MATCH(" & ForecastYear & ",'" & ProdCode & "'!C:C,0))")

Thank you for all the help.