Loop through recordset and write records to specific rows based on condition

Question

I have a recordset looping question for which I did not see a solution here that I can adapt, so I ask this as a question:

I have an Excel template for a list of line items, item 1 to 20, which are listed in rows 1 to 20 of the template. The cells A1 to A20 have the running number values "1" to "20" and this never ever changes.

I have an SQL Server database table that contains all line items of all commissioners. The table contains the commissioner name and the running numbers, among other things.

Initially, if 10 items are commissioned, they live in lines 1 - 10 of the template, and have the corresponding running number in the database. What happens then is that some of these 10 items are sold, are taken off the list, and get deleted or archived in the database.

So I end up with a recordset with running numbers 1, 3, 4 and 7, as an example. What I need is that when I populate the Excel list with the remaining records from the database, that the remaining running numbers to sit in "their" row, where cell value in row A matches with running number of record.

The code I have loops though the recordset and just slaps the 4 remaining records into lines 1 to 4.

But what I need is a loop that moves from one line of the template to the next, checks the cell value in column A, checks if there is a corresponding running number value in the recordset, puts the record in that line if true, and moves to the next line.

Maybe I have to make the value of the "row" variable dependent on which running number values I have in my recordset, but that feels awfully complex, so maybe someone with more experience has a simpler solution.

My code (which is a simple "slap everything in" loop) looks like this:

The commissioner is selected in the SQL Query, and the "row" variable is defined elsewhere and just states the row number to start with.

Sub test()
    Do While (Not rs1.EOF)
        Worksheets("Tabelle1").Select

        'I tried at different places (inside and outside of the Do...Loop)
        'If rs1!RunningNumber.Value = ActiveSheet.Range("A" & Row).Value Then
        'but that only had the effect of stopping the loop afetr the first record.
                               
        ActiveSheet.Range("B" & Row).Value = rs1!ArticleDescription.Value
        ActiveSheet.Range("F" & Row).Value = rs1!Size.Value
        ActiveSheet.Range("G" & Row).Value = rs1!CategoryID.Value
        ActiveSheet.Range("I" & Row).Value = rs1!Price.Value
        ActiveSheet.Range("L" & Row).Value = rs1!AdditionalInfo.Value
                    
        Row = Row + 1
      
        rs1.MoveNext
    Loop
End Sub

Nicholas Hunter · Answer 1 · 2021-05-05T14:49:09.823

0

A couple of general principles. (1) Try to avoid using Select in your VBA code whenever you can. Some examples of how to avoid select. (2) Move everything that does not depend on the value of the iterator out of the loop.

From your description, it sounds like you should just set the value of the worksheet target row to the value of the "running number" in the database table. Give this a try an see if it works for you.

Sub test()

    Dim ws as worksheet

    Set ws = Worksheets("Tabelle1")

    Do Until rs1.EOF
                               
        Row = rs1!RunningNumber.Value
        ws.Range("B" & Row).Value = rs1!ArticleDescription.Value
        ws.Range("F" & Row).Value = rs1!Size.Value
        ws.Range("G" & Row).Value = rs1!CategoryID.Value
        ws.Range("I" & Row).Value = rs1!Price.Value
        ws.Range("L" & Row).Value = rs1!AdditionalInfo.Value
      
        rs1.MoveNext
    
    Loop
    
End Sub

edited May 05 '21 at 14:49

answered May 05 '21 at 12:08

Nicholas Hunter

1,791
1
11
14

Thank you so much, that put me on the right track. I did not think of using the RunningNumber value to point the loop directly to the row I want it to put data in, instead I could only think of checking each row for all running numbers. One thing, though: When using "Cell" I got the error (translated from German) "Runtime Error '5': Invalid procedural call or invalid argument." What could be the reason for this? After replacing "cell" with a Worksheet / Range reference like ```Worksheets("Tabelle1").Range("B" & Row).Value)``` it now works as expected. Thanks again! – Ulrich May 05 '21 at 14:42
Ah! Thanks for the heads up. I have corrected my code in case anyone else with a similar problem comes across this answer and encounters the same error. Glad you got it working. – Nicholas Hunter May 05 '21 at 14:50

Loop through recordset and write records to specific rows based on condition

1 Answers1