第一次通过后,VBA中的VLookup循环失败

我正在尝试在VBA中使用FOR循环来遍历工作表并使用VLookup来确定基于每个范围的值 . 基本上,工作表设置在我有14个范围,每个范围是2列(日期和值)设置如下:Sample Data

此代码循环遍历每个范围并执行VLookup以返回值,如果没有值则返回-1 .

我遇到的问题是这适用于第一行数据,但此后所有行都返回-1 .

Sub Format(inSheet As String, outSheet As String, lastAvail as Date, maxRows as Long)
Do While curDate <= lastAvail
    For x = 2 To (maxRows - 1) * 2 Step 2
        ' Get value of current data series
        Sheets(inSheet).Activate
        Range(Cells(8, x - 1), Cells(8, x)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Set lookupRange = Selection
        val = Application.VLookup(curDate, Worksheets(inSheet).Range(lookupRange.Address), 2, False)
            Sheets(outSheet).Activate
            If IsError(val) Then
                Cells(curRow, x / 2 + 1).Value = -1
            ElseIf IsNumeric(val) Then
                Cells(curRow, x / 2 + 1).Value = val
            Else
                Cells(curRow, x / 2 + 1).Value = Null
            End If
    Next

    curDate = DateAdd("m", 1, curDate)
    Cells(curRow, maxRows + 1).Value = curDate - 1
    curRow = curRow + 1
    val = ""
Loop

回答(1)

2 years ago

感谢您的帮助,事实证明,当我使用 DateAdd 增加curDate时,它会更改格式,以便 VLookup 不与实际数据匹配 . 我在 VLookup 之前将curDate转换为 Long ,一切都解决了 .