HELP: simple problem with short code

[tatendatiffany]

ok so i have created a loop that run in excel sheet range "environ4" which covers AB2:AB30 executing the command in each cell. but i am gettinfg the errror "object doesnt support thid error or property" will i try to do the same with the range "column4" AD2:AD30 where i want the results to be displayed: the errror line is in bold

Public Sub LoopQueries()
Dim i As Long
Dim SQL1 As String
SQL1 = ThisWorkbook.Worksheets("Index").Range("Environ4")
[B]ThisWorkbook.Worksheets("Index").Range ("Column4")[/B]
For j = 2 To 39
If (Sheet1.Cells(i, 2) = "") Then
j = 2
Else
j = 2
i = i + 2
End If
Next
End

 

[VoG]

Presumably it should be something like

SQL2 = ThisWorkbook.Worksheets("Index").Range("Column4")

 

[tatendatiffany]

it is saying type mismatch when i use that code...may i ask why sql2 why not sql as it displaying the results from that??

 

[VoG]

It isn't at all clear what you are trying to do but you cannot assign a string variable to a multi-cell range. Maybe you need to loop through each cell in each range?

 

[dantheman9]

.

 

[tatendatiffany]

Si how would i change it because each cell that range environ4 is suppose to run .then display the result of that in the the cell in column4 so for instance a2 runs a query display this result in ab2 and a3 runs a squery display this in ab4 and so on till the end.

should i do something like this

dim cell as range
for each cell in this worksheet range ("environ4")
offset 
next c

then for display i dont know but something along these lines??

 

[VoG]

Along the lines of

For Each c In ThisWorkbook.Worksheets("Index").Range("Environ4")
    c.Offset(, 2).Value = c.Value
Next c

 

[tatendatiffany]

Thank you for responding but can i ask a question as i am trying to understand so i don't keep asking for help
-why did you use 2 on

 can i sue any nubmer or you used this one for a specific reason

(, 2)</pre>

-how is excel going to know where to place the results of each run because right now from my understanding the command its getting is telling it to run through the range "environ2" until it executes everything??
-how will it know when to stop running through the cell e.g. when it encounters a blank cell??


Thank you

 

[tatendatiffany]

sorry about the typos

 

[tatendatiffany]

Now i am having completely different problems because my loop doesnt seem to be doing anything when i run it :

Public Sub LoopQueries()
Dim SQL1 As Variant
Dim c As Range
SQL1 = ThisWorkbook.Worksheets("Index").Range("Environ4")
For Each c In ThisWorkbook.Worksheets("Index").Range("Environ4")
    c.Offset(, 2).Value = c.Value
Next c

End
' THIS IS CONNECTION TO AMA4 RUNNING THROUGH ENTIRE COLUMN

Dim SQL2 As Variant
Dim d As Range
SQL2 = ThisWorkbook.Worksheets("Index").Range("Environ1")
For Each d In ThisWorkbook.Worksheets("Index").Range("Environ1")
d.Offset(, 2).Value = d.Value
Next d

End
'THIS CONNECTS TO AM1 AND IS RUNNING THROUGHOUT THE COLUMN
Dim SQL3 As Variant
Dim e As Range
SQL3 = ThisWorkbook.Worksheets("Index").Range("Environ3")
For Each e In ThisWorkbook.Worksheets("Index").Range("Environ3")
e.Offset(, 2).Value = e.Value
Next e

End
' THIS IS CONNECTION TO AMA3 RUNNING THROUGH ENTIRE COLUMN
Dim SQL4 As Variant
Dim f As Range
SQL3 = ThisWorkbook.Worksheets("Index").Range("Environ5")
For Each f In ThisWorkbook.Worksheets("Index").Range("Environ5")
f.Offset(, 2).Value = f.Value
Next f

End


' THIS IS CONNECTION TO AM5 RUNNING THE ENTIRE COLUMN

Dim SQL5 As Variant
Dim g As Range
SQL3 = ThisWorkbook.Worksheets("Index").Range("Environ7")
For Each g In ThisWorkbook.Worksheets("Index").Range("Environ7")
g.Offset(, 2).Value = g.Value
Next g
End
End Sub