VBA - SQL Select and return value on the same row

[Rödtjut]

Hi,

I'm tryring to loop through a range of articles and return the cost price of each article.
I want the price to be returned on the same row, but I can't quite get it to work.

The correct prices gets returned but they alwyas print out two cells below where they should be.

What am I doing wrong? :crying:

Thanks!



SQLStr = "SELECT convert(money, poCOST) FROM [dbo].[AVKParmenItemExport2]" & _
"WHERE [RowNo] IN( '" & Join(Application.Transpose(ActiveSheet.Range("E40:E500").Value), "','") & "')"

Set cn = New ADODB.Connection

With cn
.CursorLocation = adUseClient
.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

Set rs = .Execute(SQLStr)
End With

p = 40

While Not rs.EOF
For v = 1 To rs.Fields.Count
ActiveSheet.Cells(p, v + 10).Value = rs.Fields(v - 1).Value
Next v
p = p + 1
rs.MoveNext
Wend


rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing