CODEWITHSUNDEEP
excelvba
DGMS89
DGMS89

Reputation: 1677

Stating a variable with cell location

I have a worksheet with multiple columns and rows of data. The relevant part of the data start when there is a cell in column a with some text (eg. ident).

I am trying to use an if to go through the cells until it find the cell with "ident" and return its row number (and assign a variable to this row number)

Code I am using:

For Each Cell In ActiveSheet.Range("A")
            If ActiveSheet.Cells.Value = "Ident" Then
                start1 = ActiveCell.Row
                Exit For
            End If
        Next Row

Problem is, the cell term is giving me an error (I am probably referencing it wrong). What do I need to use after the "for each" to go through the cells in column A, in this case?

Upvotes: 1

Views: 53

Answers (2)

user3598756
user3598756

Reputation: 29421

For Each cell In ActiveSheet.Range("A:A")
    If cell.Value = "Ident" Then
        start1 = cell.Row
        Exit For
    End If
Next

you may also consider these two further steps of improvement (from both logical and speed point of view):

  • step 1

    loop only through cells with some constant text value in it

    For Each cell In ActiveSheet.Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues)
    If cell.Value = "Ident" Then
        start1 = cell.Row
        Exit For
    End If
Next

  • step 2

    use Find() method and avoid loops

    Set cell = ActiveSheet.Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).Find(what:="ident", lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
If Not cell Is Nothing Then start1 = cell.Row

    where you must both always specify values for LookIn, LookAt and MatchValue arguments and carefully choose them

Upvotes: 2

Vityata
Vityata

Reputation: 43593

Another option for looping through the column.

Option Explicit

Public Sub TestMe()

    Dim cell As Range

    For Each cell In ActiveSheet.Columns(1).Cells
        If cell.Value = "Ident" Then
            Debug.Print cell.Row
            Exit For
        End If
    Next cell

End Sub

Upvotes: 0

Related Questions