VBA: For Each row in table (for each statement help)

[Chris Macro]

I can't get the syntax for my For Each statement, any suggestions on how to code the first line? I am wanting to loop through each row in my table.

<font face=Calibri><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Rw <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets(Data.Name).ListObjects("myStockPurchases").DataBodyRange.Rows<br>    <SPAN style="color:#00007F">If</SPAN> Rw.Columns("Stocks").Value = SectorType And Rw.Columns("Buy/Sell").Value = "Bought" <SPAN style="color:#00007F">Then</SPAN><br>        QuantityArray(m) = Rw.Columns("Quantity").Value<br>        TotalArray(m) = Rw.Columns("Price").Value<br>        m = m + 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> Rw<br></FONT>

 

[Norie]

What's wrong with the For Each? As far as I can see it's correct.

Not sure about the rest of the code though - Rw will be a standard range (row) and you can't refer to the columns in it by name.

 

[Chris Macro]

that's why I am stumped.....I am getting an "Object variable or With block variable not set" error on

[COLOR=#00007F][FONT=Calibri]For[/FONT][/COLOR][COLOR=#574123][FONT=Calibri] [/FONT][/COLOR][COLOR=#00007F][FONT=Calibri]Each[/FONT][/COLOR][COLOR=#574123][FONT=Calibri] Rw [/FONT][/COLOR][COLOR=#00007F][FONT=Calibri]In[/FONT][/COLOR][COLOR=#574123][FONT=Calibri] ThisWorkbook.Worksheets(Data.Name).ListObjects("myStockPurchases").DataBodyRange.Rows[/FONT][/COLOR]

What I am trying to accomplish is to go through each row in my table and if the "Buy/Sell" column.value = "Bought" then load the Quantity value (in another column) into an array and load the Total value (in another column) into an array. Any thoughts on how to simply accomplish this?

 

[Norie]

I don't get that error on that line, but I just test with a simple table I set up.

Mind you the code you posted isn't complete, for example how did you declare rw?

 

[AlphaFrog]

If earlier in your code you have an IF block without an End If or a For without a Next or a With without an End With ...etc., you can get an error like that and the debugger often highlights the wrong line of code. The error may have nothing to do with the ForEach Rw In... line.

 

[Chris Macro]

ok, after a bunch of testing I came up with the procedure below that gives me the functionality I was going for. If you can think of a more simplistic way give me your thoughts (I really don't like using offsets for this, but it was the only way I could think of to accomplish this task).

Code: (Dim Rw as Range)

<font face=Calibri><SPAN style="color:#007F00">'Fill Price and Quantity Arrays</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Rw <SPAN style="color:#00007F">In</SPAN> Data_sht.Range("myStockPurchases[[Buy/Sell]]")<br>    <SPAN style="color:#00007F">If</SPAN> Rw = "Bought" And Range(Rw.Address).Offset(0, 1) = SectorType <SPAN style="color:#00007F">Then</SPAN><br>        QuantityArray(m) = Range(Rw.Address).Offset(0, 2)<br>        TotalArray(m) = Range(Rw.Address).Offset(0, 3)<br>        m = m + 1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> Rw</FONT>

 

[Norie]

Why are you using Range(Rw.Address)? That's just Rw.

So Range(Rw.Address).Offset(0,1) is just Rw.Offset(0,1).

For Each Rw In Data_sht.Range("myStockPurchases[[Buy/Sell]]")
    If Rw = "Bought" And Rw.Offset(0, 1) = SectorType Then
        QuantityArray(m) = Rw.Offset(0, 2)
        TotalArray(m) = Rw.Offset(0, 3)
        m = m + 1
    End If
Next Rw

 

[Chris Macro]

Good Point....For some reason I was thinking that rw = the cell content and not the cell location. Thanks for the help!