Solved: cycle through rows

[talytech]

I want to write a program that will append data from the specified cells into the corresponding fields in my database table. I have the code that connects to the database and the code that runs the append query but what I don't know how to do is make the program loop and run the same code for each row that has data. For example .. I want the program to start at row 13 and run the code below. Then go to the next row and run the code. ... and continue until the next row has no data.

qdfAddRequest = "INSERT INTO tblKeyRequests ([SOL#], RequestorName, UserId, DateRequested, keysRequested, created, modifiedby ) " _
& "SELECT '" & solnum & "' AS solnum, '" & fullnm & "' AS rqstor, '" & usrId & "' AS usrId, '" & reqstd & "' AS datereqsted, '" & totkeys & "' AS totkeys, '" & created & "' AS created, '" & ModifiedBy & "' AS createdby;"

dbsKeys.Execute qdfAddRequest


Note: the variables have already been defined.

[Bob Phillips]

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 13 To lastRow
'set you variables
'e.g. solnum = Cells(i,"D").Value
qdfAddRequest = "INSERT INTO tblKeyRequests ([SOL#], RequestorName, UserId, DateRequested, keysRequested, created, modifiedby ) " & _
"SELECT '" & solnum & "' AS solnum, '" & fullnm & "' AS rqstor, '" & usrId & _
"' AS usrId, '" & reqstd & "' AS datereqsted, '" & totkeys & "' AS totkeys, '" & _
created & "' AS created, '" & ModifiedBy & "' AS createdby;"

dbsKeys.Execute qdfAddRequest
Next i

[talytech]

Wow .. thank you so much .. that worked.

[talytech]

XHD, how can I retrieve the total of rows filled in starting from the row 13?

[Bob Phillips]

LastRow - 13 + 1

[talytech]

This is what I have:

lastrow = Cells(Rows.Count, "B").End(xlUp).Row
lastrow = 14 + 1

MsgBox lastrow


That returned 14 but it should be 2. Rows 14 and 15 are filled in. Row 16 is empty. I need to know how many rows have been filled in counting from row 13.

[RonMcK]

talytech,

I think you may have misread XLD's formula:

lastrow - 13 + 1 (take number of last row used, subtract 13 (first row you are counting from) and add 1 since that first row counts)

So if lastrow = 14 you should have: 14 - 13 + 1 = 2 (your expected answer).

Cheers,