向下舍入 NOW（） 函数以在 VLOOKUP 中使用

请使用下一个更新的代码。它将格式化必要的范围，使公式低于范围“N1：AB1”以匹配导入的数据：

Sub openWeather()
  Const url As String = "https://api.open-meteo.com/v1/forecast?latitude=53.288&longitude=-1.2881&hourly=temperature_2m,precipitation_probability,precipitation,rain,snowfall,windspeed_10m,winddirection_10m,windgusts_10m,is_day&windspeed_unit=mph&forecast_days=1"
  Dim json As Object, ws As Worksheet, dataSet As Long, currRow As Long
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  currRow = 2
  
  With CreateObject("MSXML2.XMLHTTP.6.0")
    .Open "GET", url, False
    .send
    
    If .Status = 200 Then
      Set json = JsonConverter.ParseJson(.responseText)
      Application.Calculation = xlCalculationManual: Application.EnableEvents = False
       For dataSet = 1 To json("hourly")("time").Count
        ws.Cells(currRow, 1) = Replace(json("hourly")("time")(dataSet), "T", " ")               'timestamp
        ws.Cells(currRow, 2) = json("hourly")("temperature_2m")(dataSet)                        'temp
        ws.Cells(currRow, 3) = json("hourly")("precipitation")(dataSet)                         'rain
        ws.Cells(currRow, 4) = json("hourly")("snowfall")(dataSet)                              'snow
        ws.Cells(currRow, 5) = json("hourly")("precipitation_probability")(dataSet)             'probability
        ws.Cells(currRow, 6) = json("hourly")("windspeed_10m")(dataSet)                         'speed
        ws.Cells(currRow, 7) = json("hourly")("winddirection_10m")(dataSet)                     'direction
        ws.Cells(currRow, 8) = json("hourly")("is_day")(dataSet)                                'day / night
        currRow = currRow + 1
       Next dataSet
      Application.Calculation = xlCalculationAutomatic: Application.EnableEvents = True
    Else
      MsgBox "Page not loaded. HTTP status: " & .Status
    End If
  End With
  
  For Each cel In ws.Range("G2:G" & ws.Cells(Rows.Count, "G").End(xlUp).Row + 1).Cells


  Next cel
  
  processRangeForVlookup ws, False 'it creates the appropriate range in N1:AB1
End Sub

Sub processRangeForVlookup(ws As Worksheet, Optional boolChangeDate As Boolean = False)
 Dim rngD As Range
 'Create the necessary range to be compared:
 ' first cell to contain local time/local hour:
 ws.Range("N1").Formula = Evaluate("=FLOOR(now(), """ & Format(Hour(Now), "00") & ":00"")")
 ws.Range("O1").Formula = "=N1+(1*(1/24))": ws.Range("P1").Formula = "=O1+(1*(1/24))"
 ws.Range("O1:P1").AutoFill Destination:=ws.Range("O1:AB1")
 Set rngD = ws.Range("N1:AB1")
 With rngD
    .NumberFormat = "dd/mm/yyyy hh:mm"
    .Value = .Value
    .EntireColumn.AutoFit
 End With
 If boolChangeDate Then 'to change the date at midnight as the following one.
    Dim i As Long
    For i = 1 To rngD.Columns.Count
    If Hour(rngD.Cells(1, i).Value) = 0 Then
        rngD.Cells(1, i).Value = Int(rngD.Cells(1, i).Value) + 1
    End If
  Next i
 End If
End Sub

请在测试后发送一些反馈。