Can`t seem to get active cell row number into to my code

[Eric Penfold]

I have looked at websites ect but for some reason the code won`t pick up the active cell row number please help?
What is wrong with this part of code?

Row = .ActiveCell.Row

Says Error "Method or Data Member not Found"

Sub BOReason()

    Dim Ws             As Worksheet
    Dim LRow           As Long, Row As Range
    Dim x              As Variant, y As Variant, i As Variant
    Dim Rng            As Range, Comparerng As Range
    Dim YDat           As Date
    Dim TDat           As Date
    Dim StartTime      As Double
    Dim SecondsElapsed As Double
   
    StartTime = Timer
   
    With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With

       

        Set Ws = ActiveSheet
        LRow = Ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        YDat = Format(CDate(Application.WorksheetFunction.WorkDay(Date, -1)), "dd/mm/yyyy")
        TDat = CDate(Date)

        On Error Resume Next
        With Ws
            Set Rng = .Range("A2:A" & LRow).Find(YDat, LookIn:=xlValues, LookAt:=xlWhole)
            Row = .ActiveCell.Row

        With Rng
             If Rng Is Nothing Then
                .Range("A1").AutoFilter 1, Format(TDat, "dd/mm/yyyy")
            Else
                .Range("A1").AutoFilter 1, Format(TDat, "dd/mm/yyyy"), 2, Format(YDat, "dd/mm/yyyy")
             End If
             End With

          If Ws.Name <> "Summary" And Ws.Name <> "Trend" And Ws.Name <> "Supplier BO" And Ws.Name <> "Dif Depot" Then

            Set Comparerng = .Range("E2:E" & LRow)

                For Each x In Comparerng.SpecialCells(xlCellTypeVisible).Select
                For Each y In Comparerng.SpecialCells(xlCellTypeVisible).Select

                       If x = y Then
                             .Range("J" & y.ActiveCell.Row) = .Range("J" & x.ActiveCell.Row)
                        Else
                             Exit Sub
                        End If
                    Next y
               Next x
            End If

        .Range("AA1") = ""
        End With

    With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    End With


    Ws.ShowAllData

SecondsElapsed = Round(Timer - StartTime, 2)

MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

 

[davesexcel]

You have Row dimmed as a "range", change it to a "long"

 

[Eric Penfold]

I`ve changed it to Long but still not working.

 

[davesexcel]

Is it possible you can't use 'row' as a variable?
Is ws the activesheet when you use activecell?

 

[gordsky]

take the full stop out before activecell, should work then so

Row = ActiveCell.Row

 

[Eric Penfold]

You mean call it a Variant

 

[Eric Penfold]

take the full stop out before activecell, should work then so

Row = ActiveCell.Row

If you do that it says "Expected Function or Variable"

 

[RoryA]

On which line?

It might be simpler if you explained what you are trying to do as a lot of this code makes no sense at all - eg:

        With Rng
             If Rng Is Nothing Then
                .Range("A1").AutoFilter 1, Format(TDat, "dd/mm/yyyy")

How can you possibly refer to RNG.Range("A1") if you just determined that RNG is Nothing?

 

[gordsky]

If you do that it says "Expected Function or Variable"

Eric, I dont get that error at all when row is dim as long, runs through nicely and returns row number
The only reason I can see for that happening is if you have another variable or sub with the same name

 

[Eric Penfold]

On which line?

It might be simpler if you explained what you are trying to do as a lot of this code makes no sense at all - eg:

        With Rng
             If Rng Is Nothing Then
                .Range("A1").AutoFilter 1, Format(TDat, "dd/mm/yyyy")

How can you possibly refer to RNG.Range("A1") if you just determined that RNG is Nothing?

The Rng is to find the yesterdays date if it can`t find it it goes to else

Set Rng = .Range("A2:A" & LRow).Find(YDat, LookIn:=xlValues, LookAt:=xlWhole)

        With Rng
             If Rng Is Nothing Then
                .Range("A1").AutoFilter 1, Format(TDat, "dd/mm/yyyy")
       Else
               .Range("A1").AutoFilter 1, Format(TDat, "dd/mm/yyyy"), 2, Format(YDat, "dd/mm/yyyy")

This seems to work.
I am talking about the Active Cell row number