excel - 读取链接到多个列表验证 vba 的单元格

Question

我正在尝试更新 3 个不同的列表验证单元格，遍历 3 个的所有可能组合，然后读入一些单元格值。

循环似乎工作正常，并正确更改列表值，但单元格值的最终分配给我的其他工作簿似乎不起作用。首次打开工作表时，它会获取单元格中的值，但它们不会根据列表验证单元格中的每个新条目进行更新。

谁能告诉我我哪里出错了？我是在分配单元格吗

下面是我正在进行循环的相关代码

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

标签： excelvba