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