Hello all, and thanks in advance for any help you can offer. I visit often and have picked up a lot from the users here, but have run into a problem that my so-so skills in VBA can't solve.

I have a workbook set up that automatically refreshes a web query every 15 minutes, and shows the work performed in various processes in the warehouse that i work for. This rows in the web query table can vary from refresh to refresh, depending on whether or not that work process was run during a given 15 minute window.

There is a predefined list of 32 potentially 'active' processes that can appear, and I have a table set up listing each of them in columns. In the rows of that table, I want to list the time as of the last refresh (that was easy, I have a macro to find the first empty row and populate the time of the refresh in column 1), and either a zero if the process doesn't appear on the latest refresh of the web table, or a specific data point if the process DOES appear on a data table. For example:

In my Static table I have 'Time', 'Small', 'Medium', 'Large', 'Multi', 'Other' as column headers

My web query will only show processes which were active as of the last refresh, and may look like this:

Time Process Name Units Picked
9:00a Small 156
9:00a Large 45

So I am curious to know, since my web query may or may not show all of the processes, is there a way to have it do a lookup of sorts against a master list, and populate that data in the next empty row for that column in a table, and record a 0 if it's not found?

The result I'm looking for is something like this, in my 'static' table based on my sample above:

Time 'Small' 'Medium' 'Large' 'Muti' 'Other'
9:00a 156 0 45 0 0

and have it populate rows downward at each refresh. I wrote some VBA that would work perfectly if the web query processes were always in the same cells upon refresh, but since processes can be added/deleted 'Small' might be in cell B8 on one refresh, but in B10 on another (or gone entirely). That's how I found out the web query table had changing row data... my charts were out of whack.

Here's what I have that works if the web query table rows are always the same (of course they're not)...,

Sub UpdateWebQuery()

On Error Resume Next

Dim sngSmall As Single
Dim sngMedium As Single
Dim sngLarge As Single
Dim sngMulti As Single
Dim sngOther As Single
Dim N As Integer


With ThisWorkbook.Sheets("WebQuery")
.QueryTable.Refresh BackgroundQuery:=False
sngSmall = .Range("D5").Value
sngMedium = .Range("D26").Value
sngLarge = .Range("D44").Value
sngMulti = .Range("D61").Value
sngOther = .Range("D79").Value

End With

With ThisWorkbook.Sheets("Static Data")
N = FindEmptyRow("Static Data")
.Cells(N, 1).Value = Time
.Cells(N, 2).Value = sngSmall
.Cells(N, 3).Value = sngMedium
.Cells(N, 4).Value = sngLarge
.Cells(N, 5).Value = sngMulti
.Cells(N, 6).Value = sngOther

End With

Any thoughts, comments, and help is very much appreciated. Thanks!