Looping Query using value from next cell

Excel Help for Looping Query Using Value From Next Cell in Automating Data Analysis Tasks

Looping Query Using Value From Next Cell

Rate this: (3/5 from 1 vote)

	I have query that loops a number of times depending on the number of rows in a column that contains data.  I am having troubles figuring out how to make it use the first row of that column in the query VBA Code: strTAB = Worksheets("Data_Col").Range("I5").Value and then when it loops the next time use the 2nd row of that column VBA Code: strTAB = Worksheets("Data_Col").Range("I6").Value  until it reaches the last row of that column and has returned all the data.
	Posted by Torantis on 27 Sep 2007

Replies - Displaying 1 to 6 of 6

Order Replies By: Most Recent | Chronological | Highest Rated

	Dynamic ranges and offsets Rate this: (3/5 from 1 vote) You can loop down a selection using dynamic ranges and cell offset to get the value from the next cell.  For example: VBA Code: Dim curcell As Variant Dim nxtval As Variant  Range("I5").Select Range(ActiveCell, Selection.End(xlDown)).Select For Each curcell in Selection   nxtval = curcell.offset(1,0).value  Next
	Excel Business Forums Administrator
	Posted by Excel Helper on 27 Sep 2007

	? Rate this: (3/5 from 1 vote) Ok I understand that part but am unsure how I would specify what strTAB is.  Would it be strTAB = curcell
	Posted by Torantis on 28 Sep 2007

	strTAB would be nxtval Rate this: (3/5 from 1 vote) strTAB would be nxtval as it is the next row value that you want to return to the strTAB variable in your first post.
	Excel Business Forums Administrator
	Posted by Excel Helper on 28 Sep 2007

Thx....one more thing.. Rate this: (3/5 from 1 vote) Great that worked for me.  Thank you.  I had to get rid of my original loop because it would loop through then loop through again. One more thing on this.  After I put this in and got rid of my other loop it seems to go through each cell with data then an additional one because it returns an error that I put into the sheet that if recordset = 0.  I verified that all the data was returned for what was listed in that column and it was. Also tried to update a pivot table for this like.... VBA Code:     With Worksheets("Data_Col").PivotTables("PivotTable3").PivotFields("PROCESS_BATCH")     .PivotItems(" " & nxtval & " ").Visible = True     End With Gives error Unable to get the PivotItems property of the PivotField class.  It does however under debug display the correct batch number.

Posted by Torantis on 28 Sep 2007

	Last value and quotes Rate this: (3/5 from 1 vote) The first issue maybe due to the final iteration of the loop return the empty cell at the end of the range selected.  This could be caught like: VBA Code: If nxtval <> "" Then    'do whatwever End if The second issue could be due to the double quotes around the nxtval. Have you tried to remove them?
	Excel Business Forums Administrator
	Posted by Excel Helper on 28 Sep 2007

	Quotes Rate this: (3/5 from 1 vote) For the second issue I have tried removing them and that does not work.  Under debug as it is right now it returns the correct value.  When I remove them it doesn't return anything.  One of my co-workers mentioned he has seen issues in the past with pivot tables if the value is considered an integer and trying to update it with that value.
	Posted by Torantis on 28 Sep 2007

Displaying page 1 of 1