Setting a variable equal to a row number

Question

I need to set EndOfFile and DataRange = to the row number they reside in.

Thanks in advance,

Scott

Dim NumOfRows As Integer
    Dim EndOfFile As Range
    Dim DataRange As Range 

Sub Find_xy_data()
    Dim RowArray(1 To 10000) As Range

    'Counters
    i = 2


       ' Locate last line of KLARF location data
    Cells.Find(What:="EndOfFile;", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Set EndOfFile = ActiveCell.Rows



    ' Locate first line of KLARF location data
    Cells.Find(What:="DefectRecordSpec", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
       Set DataRange = ActiveCell

Xabier · Accepted Answer · 2018-02-09 17:37:22Z

1

How about the following:

Sub Find_xy_data()
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    'declare and set the worksheet above, amend as required

    Dim EndOfFile As Range
    Dim DataRange As Range

    Set EndOfFile = ws.Cells.Find(What:="EndOfFile;")
    'find and set variable to range at which it was found
    Set DataRange = ws.Cells.Find(What:="DefectRecordSpec")
    If Not EndOfFile Is Nothing Then MsgBox "EndOfFile found at row " & EndOfFile.Row
    'if variable is not empty then found, prompt with msgbox
    If Not DataRange Is Nothing Then MsgBox "DefectRecordSpec found at row " & DataRange.Row
End Sub

edited Feb 9, 2018 at 17:37

answered Feb 9, 2018 at 17:27

Xabier

7,7251 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darren Bartrup-Cook · Accepted Answer · 2018-02-09 17:30:43Z

1

First you need to find the cell.
You can't return the row number or activate the cell if it's not found; an error will occur.

Sub Test()

    Dim EndOfFile As Range

    Set EndOfFile = Sheet1.Cells.Find(What:="EndOfFile;", After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)

    If Not EndOfFile Is Nothing Then
        MsgBox EndOfFile.Address & " is on row " & EndOfFile.Row
    Else
        MsgBox "Not Found"
    End If

End Sub

answered Feb 9, 2018 at 17:30

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

1 Comment

Scott Over a year ago

Thanks very much, I won't have a chance to work on this until later but I wanted to say it's much appreciated.

Collectives™ on Stack Overflow

Setting a variable equal to a row number

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related