Of course this is possible, but I need to know how you want it to work. For
example:

Lets add a parameter to your function definition:

Sub Sales_Query(rangTarget as range)

So later on you can use:

salesrep = rangTarget .Text
salesdate = rangTarget .offset(1,0).Value

....

Destination:=rangTarget .offset(3,-1)

....

There are a lot of other possibilities & considerations, how to pass
rngTarget to this procedure, what if you are in column A (the offset(3,-1)
would be illegal) etc.

However I hope this give you some food for thought and you will come back
with further info/clarification.





"jimmygoogle" wrote:

i have a spreadsheet where there is a field call it 'name' and when
the user types in the name it returns the result matching the where
clause - is it possible that i do not have to hardcode the cell number
so the user can enter and return as many result sets as they want

i dont have VB experience but i have programming experience so i was
hoping to follow this model i found but I would like to not have to
hardcode C2, C3, etc - i dont know if this is even possible - any
insight is appreciated

Sub Sales_Query()
Dim salesrep As Variant
Dim salesdate As Date

salesrep = Range("C2").Text
salesdate = Range("C3").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Northwind;Description=Northwind;APP=Micr osoft Office
XP;DATABASE=Northwind;Trusted_Connection=YES"),
Destination:=Range("B5"))
.CommandText = Array("SELECT * FROM Orders WHERE EmployeeID="
& salesrep & " AND OrderDate '" & salesdate & "' ORDER BY
OrderDate")
.Name = "Sales Query from Northwind"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

On Jun 5, 2:46 am, Ben McBen
wrote:
Of course this is possible, but I need to know how you want it to work. For
example:

Lets add a parameter to your function definition:

Sub Sales_Query(rangTarget as range)

So later on you can use:

salesrep = rangTarget .Text
salesdate = rangTarget .offset(1,0).Value

...

Destination:=rangTarget .offset(3,-1)

...

There are a lot of other possibilities & considerations, how to pass
rngTarget to this procedure, what if you are in column A (the offset(3,-1)
would be illegal) etc.

However I hope this give you some food for thought and you will come back
with further info/clarification.

"jimmygoogle" wrote:
i have a spreadsheet where there is a field call it 'name' and when
the user types in the name it returns the result matching the where
clause - is it possible that i do not have to hardcode the cell number
so the user can enter and return as many result sets as they want

i dont have VB experience but i have programming experience so i was
hoping to follow this model i found but I would like to not have to
hardcode C2, C3, etc - i dont know if this is even possible - any
insight is appreciated

Sub Sales_Query()
Dim salesrep As Variant
Dim salesdate As Date

salesrep = Range("C2").Text
salesdate = Range("C3").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Northwind;Description=Northwind;APP=Micr osoft Office
XP;DATABASE=Northwind;Trusted_Connection=YES"),
Destination:=Range("B5"))
.CommandText = Array("SELECT * FROM Orders WHERE EmployeeID="
& salesrep & " AND OrderDate '" & salesdate & "' ORDER BY
OrderDate")
.Name = "SalesQueryfrom Northwind"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


I guess salesrep I would want starting in column A2 and salesdate in
B2 with the results starting in D2
and have is be possible the user could enter stuff in A4,B4 then
A6,B6, etc (every other one). Does this help?