How to reference a cell in a loop?

Question

I am looping through a row while looking for the string "Yes". If the string is found, I want to copy the value in the cell one column to the left of it and paste it in another worksheet.

The part of my code I'm struggling with is referencing the cell offset the cell that contains the string "Yes". Would the code not simply be something like ActiveCell.Offset(-1,0)?

I might be thinking about this the wrong way so I am open to suggestions.

Sub This_One_Will_Work() Dim Y_N_Column As Integer, LastColumn As Integer, CurrentRow As Integer, LastRow As Integer, New_Market_Tracker As Range 'need ActiveRow as variable and for it to go up 3 times Y_N_Column = 2 Last_Open_Row = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Rows.Count Last_Tracker_Row = ThisWorkbook.Worksheets("Market Tracker").UsedRange.Rows.Count Set New_Market_Tracker = ThisWorkbook.Worksheets("Market Tracker Template").Range("A1:T1") 'ActiveCell = cell with the offset the "Yes" cell in the Y_N_Column Do While Y_N_Column <= Last_Open_Row If ThisWorkbook.Worksheets("Market to Open").Cells(1, Y_N_Column).Value = "Yes" Then ActiveCell.Offset(0, -1).Copy Sheet6.Range("A1").Offset(Last_Tracker_Row, 0) 'End If End If Y_N_Column = Y_N_Column + 1 Loop End Sub

Cells(1, Y_N_Column-1).Value would work; similar to using offset you listed for the activecell but not requiring anything beyond the -1 to the column — Cyril
– Cyril, Commented Jul 12, 2019 at 19:02
@ak40837 And in addition, you can't use ActiveCell like this. — AAA
– AAA, Commented Jul 12, 2019 at 19:30

BruceWayne · Accepted Answer · 2019-07-12 19:03:00Z

Do you need VBA? You could use a formula:

=INDEX($A$2:$A$7,SMALL(IF(B$2:B$7="Yes",ROW(A$2:A$7)-ROW(A$2)+1),ROWS(A$2:A2)))

Where the A range is the data you want to pull over, and the B range is where you have "Yes":

Enter as an array formula with CTRL+SHIFT+ENTER and drag down.

(You can wrap that formula in IFERROR([formula],"") to hide the #NUM! error that shows up when you run out of matches.)

Thanks for the input, your formula works. However, I think VBA is the better solution for this project.

AAA · Accepted Answer · 2019-07-15 15:30:13Z

Sub This_One_Will_Work() Dim Y_N_Column As Long, Last_Tracker_Row As Long, Last_Open_Row As Long Dim LastRow As Integer, New_Market_Tracker As Range Y_N_Column = 2 Last_Open_Row = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Rows.Count Last_Tracker_Row = ThisWorkbook.Worksheets("Market Tracker").UsedRange.Rows.Count Set New_Market_Tracker = ThisWorkbook.Worksheets("Market Tracker Template").Range("A1:T1") Do While Y_N_Column <= Last_Open_Row With ThisWorkbook.Worksheets("Market to Open") If .Cells(1, Y_N_Column).Value = "Yes" Then Sheet6.Cells(Last_Tracker_Row+1, 1).Value = .Cells(1, Y_N_Column-1).Value End If End With Y_N_Column = Y_N_Column + 1 Loop End Sub

Have you tried this code yourself? I gave it a try and haven't had any luck. I don't see why your code wouldn't work, though.
@ak40837, there is no reason it shouldn't work. What error did you have, and on what line?
There's no error, there's just no string that appears. Might be an issue I have resolve on my end.
@ak40837 probably your declarations are wrong. If you look in your question, you'll see you are using different variables from what you declared. Why not step through the code to see where the issue is? What's Y_N_Column, for e.g.?

ak40837 · Accepted Answer · 2019-07-24 11:28:46Z

This is the answer to what I was trying to achieve:

Sub For_Each_SKA() Dim last_open_column As Long Dim new_market_tracker As Range Dim last_tracker_row As Long Dim last_open_row As Long last_open_column = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Columns.Count Set new_market_tracker = ThisWorkbook.Worksheets("Market Tracker Template").Range("A1:T1") last_open_row = ThisWorkbook.Worksheets("Markets to Open").UsedRange.Rows.Count For c = 1 To last_open_column last_tracker_row = ThisWorkbook.Worksheets("Market Tracker").Range("A" & Rows.Count).End(xlUp).Row If ThisWorkbook.Worksheets("Markets to Open").Cells(1, c).Value = "SKA" Then new_market_tracker.Copy ThisWorkbook.Worksheets("Market Tracker").Range("A" & last_tracker_row + 3) End If For r = 2 To last_open_row last_tracker_row = ThisWorkbook.Worksheets("Market Tracker").Range("A" & Rows.Count).End(xlUp).Row If ThisWorkbook.Worksheets("Markets to Open").Cells(r, c + 1).Value = "Yes" Then ThisWorkbook.Worksheets("Market Tracker").Range("A" & last_tracker_row + 1).Value = _ ThisWorkbook.Worksheets("Markets to Open").Cells(r, c).Value End If Next r Next c End Sub

Collectives™ on Stack Overflow

How to reference a cell in a loop?

3 Answers 3

1 Comment

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Related