Home > Forum Home > Automating Data Analysis Tasks > Looping Query using value from next cell Share

Looping Query using value from next cell

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


Forum TopicPost Reply Login

Looping Query Using Value From Next Cell

Rate this:
(3/5 from 1 vote)
Confused
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 on
 
Replies - Displaying 1 to 6 of 6Order Replies By: Most Recent | Chronological | Highest Rated
Confused
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 on
Confused
?
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 on
Happy
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 on
Confused
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 on
Surprised
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 on
Confused
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 on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Looping Query using value from next cell in the

© Business Spreadsheets   Legal   Follow us: