![VBA 查找函数无法从循环外部拉取字符串](https://linux22.com/image/1593556/VBA%20%E6%9F%A5%E6%89%BE%E5%87%BD%E6%95%B0%E6%97%A0%E6%B3%95%E4%BB%8E%E5%BE%AA%E7%8E%AF%E5%A4%96%E9%83%A8%E6%8B%89%E5%8F%96%E5%AD%97%E7%AC%A6%E4%B8%B2.png)
我正在编写代码来转换同一文件夹内多个工作簿中的温度数据 - 数据看起来像这样,只是作为工作表:
Site Id Date TMAX.D-1 (degC) TMIN.D-1 (degC) WSPDV.D-1 (mph) RHENC.D-1 (pct) LRADT.D-1 (lang)
2031 5/5/2019 21.6 5 6.1 64 382.83
2031 5/6/2019 25.9 13 8.8 68 348.68
2031 5/7/2019 17.3 9.4 9 70 258.88
用户选择是否需要转换温度(如果 TC = 1 则将“是/否”答案拉到问题消息框),然后给我要保存为字符串的列标题:
If TC = 1 Then
Dim RawTemp_max As String
Dim RawTemp_min As String
Dim TempUnits As String
RawTemp_max = Application.InputBox("Type or copy in the column label of your maximum temperature data", "Data Select", Type:=2)
RawTemp_min = Application.InputBox("Type or copy in the column label of your minimum temperature data", "Data Select", Type:=2)
TempUnits = Application.InputBox("Type the UNITS your temperature data is in, exactly as seen here: F, K", "Units", Type:=2)
到目前为止一切顺利——列标题在所有工作簿中都是一致的,因此这不应该导致问题。然后我有一个循环依次打开每个工作簿,找到温度列,添加新列并执行计算(myFile 在子文件的开头定义,不包含在这里,它是要打开的文件):
'Loop through each Excel file in folder
Do While myFile <> ""
Workbooks.Open FileName:=(myPath & myFile)
Set wb = ActiveWorkbook
Dim bname As String * 5
bname = wb.name
Dim ws As Worksheet
Set ws = wb.Sheets(1)
DoEvents
'Setup for all
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Temperature
If TC = 1 Then
Dim MaxTemp As Range
Dim MinTemp As Range
Dim xT As Range
Dim nT As Range
Dim NewMax As Range
Dim NewMin As Range
With ws.UsedRange
Set xT = .Find(RawTemp_max, LookIn:=xlValues, lookAt:=xlWhole)
If Not xT Is Nothing Then
Set MaxTemp = Range(Cells(1, xT.Column), Cells(LastRow, xT.Column))
MaxTemp.EntireColumn.Offset(0, 2).Insert
Set NewMax = Range(Cells(2, MaxTemp.Column + 1), Cells(LastRow, MaxTemp.Column + 1))
End If
End With
End If
Loop
似乎无法在 <.Find(RawTemp, LookIn:=xlValues)> 函数中找到 RawTemp 指定的温度列,因为当我尝试设置范围时,它在下一行不断给我一个“错误 91:未设置对象变量或 With 块变量”。我尝试使用 xT.select 进行故障排除,但它根本找不到单元格。
我尝试不使用 With 块(编码为 ws.Find(RawTemp, LookIn:=xlValues))并得到相同的错误。您不能将字符串传递到循环中吗?我已经检查过,字符串和列标题之间不应该不匹配。
谢谢!