Excel/ paste next Column

I added a needed space before the AND.

sql = "Select (Price + Round((Price * 0.04)*20,0)/20) from priceszone where priceplanID = 2834" & _
" AND (ZONEFROMID = " & sh.[A2] & " OR ZONETOID = " & _
sh.[C2] & ") AND ZoneToID NOT IN (1266,1267,1268,1269,1270,1271,1272,1273)"

Select allOpen in new window

You might get better performance with this:

sql = "Select (Price + Round((Price * 0.04)*20,0)/20) from priceszone where priceplanID = 2834" & _
" AND (ZONEFROMID = " & sh.[A2] & " OR ZONETOID = " & _
sh.[C2] & ") AND ZoneToID NOT BETWEEN 1266 AND 1273"

Select allOpen in new window

Hello aikimark,
My Code is gwtting the information correctly,
my issue is with getting the zonefrom, and zone to from Range Column A2, AND Column C2 and to paste the information on next column.

Example:
AND (ZONEFROMID = " & sh.[A2] & " OR ZONETOID = sh.[C2] & ")
                                                    A2 = 319                           C2= 319
paste into Column E,

THEN get next raw
AND (ZONEFROMID = " & sh.[A3] & " OR ZONETOID = sh.[32] & ")
                                                    A3 = 320                           C3= 320
paste into Column F,

THEN get next raw
AND (ZONEFROMID = " & sh.[A4] & " OR ZONETOID = sh.[C4] & ")
                                                    A4 = 321                           C4= 321
paste into Column G,

And so on.

Thanks,

how many rows does your current query return?

same number of rows as in Column A
so, in this example,
it returns 140 rows.

I think you will need to repeat the process four times, using the different ZONEFROMID and ZONETOID values in the A and C columns and specifying different (columnar) destinations with each invocation.  You can put this in a looping structure.

Example:

Sub Complete_All_in_One()
Dim lngLoop As Long
Dim sh As Worksheet
Dim cnn As ADODB.Connection
Set sh = Sheets("Crossed")
Dim rs As New ADODB.Recordset
Dim sql As String
Set cnn = New ADODB.Connection
    
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=xxxxx!;Initial Catalog=data;Data Source=Laptop;"

    'Delete old data
    sh.Range("E2:Z65000").ClearContents
   
For lngLoop = 1 To 4
sql = " Select (Price + Round((Price * 0.04)*20,0)/20) from priceszone where priceplanID = 2834" & _
"AND (ZONEFROMID = " & sh.cells(lngLoop, 1) & " OR ZONETOID = " & _
sh.cells(lngLoop, 3) & ") AND ZoneToID NOT IN (1266,1267,1268,1269,1270,1271,1272,1273)"

    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.cells(1, lngLoop + 4).CopyFromRecordset rs
Next

    rs.Close
    cnn.Close

   MsgBox "Done"
End Sub

Select allOpen in new window

Hello,
Appreciate your help,
I get error
Runtime error 3705
Operation is not allowed when the object is open.

Perfect,
Thank you