Search code examples
excelvbavlookup

Rounding down NOW() function for use in VLOOKUP

enter image description hereMorning all. A quick question for you to ponder this morning, the answer of which is probably quite easy for some.... but for me I can't fathom it hehe

So i have some data being pulled from api.open-meteo.com, just some weather data split up by the hour, as you can see in the picture. I want to separate the data showing current hour and 8 hours after the current time on a HUD display using the data on the right (which is the HUD in progress). The times on the right need to be dynamic as you'd think, the current hour 8 after.

I was using the floor method to round down the first 'time' to the hour, and then subsequently just adding one hour to every column using the formulas:

=FLOOR(NOW(),"1:00")
=N1+(1*(1/24))
=O1+(1*(1/24))

The main problem being I can't use the VLOOKUP with the above formulas because for some reason it isn't working, even though if I pop in a formula like =N1=A10 it comes up as true.

Any ideas as a work around? Would prefer a simple setup with just cell formulas but I don't mind diving into VBA territory if required!

Any ideas floating around?

https://temp-file-share.web.app/d/egJUZfM2tW

Solution

  • Please, use the next updated code. It will format the necessary range in a way to make formulas bellow the range "N1:AB1" to match the imported data:

    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

    Please, send some feedback after testing it.