I have the following code which loops through values in the Column A and
uses the value from Column A in a Query that returns data from a database
and inserts the returned data to columns C and D.
(It also loops through all the sheets in the workbook)

After 8 rows, the population becomes a column insertion. If I limit the loop
to the first 8 rows, everything works fine. After 8 rows, I get new C and D
columns for every iteration of my loop.

Any help would be greatly appreciated.

Thanks






Sub GetInfo()

Dim strSqlString As String
Dim strConnString As String

Dim shtSheet As Worksheet
Dim lngX As Long

Dim rngCell As Range
Dim rngCol As Range
Dim intLastRow As Integer

For Each shtSheet In ActiveWorkbook.Sheets

Select Case MsgBox("Get info for the items listed on the " &
shtSheet.Name & " Sheet?", vbYesNoCancel, "Get Sheet Info?")
Case vbYes
shtSheet.Activate

'Determine Last Used Row and use for Selection Range
intLastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count

For lngX = 2 To intLastRow
DoEvents
If Trim(Range("B" & lngX).Text) < "" Then
'Query Database and return data to current row and
column D
strSqlString = "SELECT im.sales_pricing_unit as UOM,
il.standard_cost * im.sales_pricing_unit_size as COMM FROM inv_mast as im
(NOLOCK), inv_loc as il (NOLOCK) WHERE im.item_id = '" & Trim(Range("B" &
lngX).Text) & "' AND il.inv_mast_uid = im.inv_mast_uid"
strConnString = "ODBC;DSN=MyDSN;Database=MyDB"
With shtSheet.QueryTables.Add(Connection:=strConnString ,
_
Destination:=Range("C" & lngX),
Sql:=strSqlString)
.BackgroundQuery = False
.FieldNames = False
.Refresh
End With
End If
Next
Case vbCancel
Exit Sub
End Select

Next shtSheet

End Sub