Reading in cells linked to multiple list validations

seanbarker

New Member
Joined
Apr 8, 2020
Messages
3
Platform
Windows
I am trying to update 3 different list validation cells, running through all possible combinations of the 3, and then read in some cell values.

The loop seems to work fine, and changes the list values correctly, but the final assignment of the cell values to my other workbook doesnt seem to work. It takes the values in the cells when the sheet is first open, but they dont update based on each new entry in the list validation cells.

Can anyone tell me where im going wrong?

Below is the relevent code where i am doing the looping

VBA Code:
For i = LBound(countries) To UBound(countries) - 1
    'close and repopen workbook as seems to be causing issues
    'emissions_wb.Close
    'Set emissions_wb = Workbooks.Open(filepath & filename)
    'Set emissions_wb = Workbooks(filename)
    'Set sheet = emissions_wb.Sheets(2)
    countries_drop_down_cell = countries(i)
    'create unique airport list for specific country
    airports = airports_by_country(i)
    
    Application.Calculate
    'if statement as last entry is just one airport so not a variant hence cant use UBound method
    
    For j = LBound(airports) To UBound(airports)
    
        airports_drop_down_cell = airports(j, 1)
        Application.Calculate
        For k = LBound(years) To UBound(years)
            Count = Count + 1
            years_drop_down_cell = years(k)
            
            'recalculate sheet
            Application.Calculate
            
            'store variables in another workbook
            data_wb.Sheets(2).Range(("D" & CStr(Count))) = years(k)
            data_wb.Sheets(2).Range(("C" & CStr(Count))) = airports(j, 1)
            data_wb.Sheets(2).Range(("B" & CStr(Count))) = countries(i)
            data_wb.Sheets(2).Range(("E" & CStr(Count))) = sheet.Range("w24").Value
            data_wb.Sheets(2).Range(("F" & CStr(Count))) = sheet.Range("w26").Value
        Next k
    Next j
Next i
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,099,141
Messages
5,466,917
Members
406,507
Latest member
annemiranda8

This Week's Hot Topics

Top