Excel VBA How do I set active cell to be only a cell with X data?

Question

 Dim NA As String
         NAFinder = 9
         NA = ActiveSheet.Range("F9").Select
        
        For i = 0 To NARows
            NA = ActiveCell.Offset(i, 0).Value
            If NA = "" Then Exit For
            If NA = "N/A" Or NA = "N\A" Then
                ActiveCell.Copy Worksheets("Folha1").Range("Q1")
              End If
        Next i

This is the code I'm currently using, the final goal here is to go through the F column and whenever N/A or N\A is found to copy the cell, place it on another sheet and then delete the line, but right now the only thing I want it to do first is to copy the cell correctly, which it's not doing, It's just copying the value in F9. Ignore the variable NAFinder, since it's not in use and I only plan to use it in the future.

I'm sure my mistakes are obvious and I'm just not seeing them, I'm very new to this stuff haha.

Thank you in advance!

Answer 1

You're getting the problem because you select F9 at the beginning, and never change the selection to the new cell. So you could use this instead:

    NA = ActiveSheet.Range("F9").Select
    
    For i = 0 To NARows
        NA = ActiveCell.Offset(i, 0).Value
        If NA = "" Then Exit For
        If NA = "N/A" Or NA = "N\A" Then
            ActiveCell.offset(i,0).Copy Worksheets("Folha1").Range("Q1")
        End If
    Next i

Having said that, I'm not a huge fan of using Range.Offset(i,0) to identify a cell. I would suggest using two Range variables and looping through them, rather than relying upon Offset(i,0).

I would also suggest finding your end cell at the beginning, rather than checking each time to see if the value is "".

Third suggestion would be rather than using multiple OR statements, use a constant that holds all the possible flagged values, and check to see if the cell contents appears in that list.

Sub ExtractNA()
    Const csFlags As String = "N\A,N/A,NA"
    Const csStCol As String = "F"
    Const csStRow As String = "9"
    
    Dim rngAll As Range
    Dim rngCell As Range
    Dim rngTarget As Range
    Dim LR As Long
    
    LR = Range(csStCol & Rows.Count).End(xlUp).Row
    
    Set rngAll = Range(csStCol & csStRow, csStCol & LR)
    Set rngTarget = Sheets("Folha1").Range("Q1")
    
    For Each rngCell In rngAll
        If InStr(1, csFlags, rngCell.Value, vbTextCompare) <> 0 Then
            rngTarget.Value = rngCell.Value
            Set rngTarget = rngTarget.Offset(1, 0)
        End If
    Next rngCell
End Sub

We define values that we expect to be fixed in Constants at the beginning, so that we can refer to them easily and change them without having to comb through the code.

We start by finding the last row in the source column that has a value, by going to Range("F" & rows.count) then using the End(xlup) method to find the bottom cell in that column that contains data, and return its .row to be the end of our data.

We then define the range of cells we want to check using rngAll, going from the cell specified in the constant with Range(csStCol & csStRow to the last row we just found.

By looping through a range a range variable, we don't need to worry about selecting anything, which will speed up processing. It's also faster to use rngTarget.value = rngCell.value instead of rngCell.copy, since it only copies the value and doesn't need to read all the cell contents into memory.