(Solved) – Reading in cells linked to multiple list validations vba


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? Am i assigning the cells

Below is the relevent code where i am doing the looping

For i = LBound(countries) To UBound(countries) - 1
    'close and repopen workbook as seems to be causing issues
    '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)

    '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)
        For k = LBound(years) To UBound(years)
            Count = Count   1
            years_drop_down_cell = years(k)

            'recalculate sheet

            '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 'issue is here
            data_wb.Sheets(2).Range(("F" & CStr(Count))) = sheet.Range("w26").Value  'issue is here
        Next k
    Next j
Next i

Leave a Reply

Your email address will not be published. Required fields are marked *