CODEWITHSUNDEEP
excelvba
ak40837
ak40837

Reputation: 13

How to reference a cell in a loop?

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

Upvotes: 0

Views: 780

Answers (3)

ak40837
ak40837

Reputation: 13

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

Upvotes: 0

AAA
AAA

Reputation: 3670

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

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

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 image description here

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.)

Upvotes: 1

Related Questions