我正在尝试在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 回答
感谢您的帮助,事实证明,当我使用
DateAdd
增加curDate时,它会更改格式,以便VLookup
不与实际数据匹配 . 我在VLookup
之前将curDate转换为Long
,一切都解决了 .