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

 

[Eric Penfold]

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

I haven`t got that.
Could I send my workbook to you to fix?

 

[gordsky]

you can attach a sample workbook here using xl2bb. I can have a look at your data and combine with the code above

 

[Eric Penfold]

Here you go thanks in advance

2022 Coventry Back OrderTest.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Order Date	Customer Name	Order No.	Ent By	Code	Description	BO Qty.	Intact Stock Levels	Notes	BackOrderReasonCode
2	01/07/2022	Entex Projects Ltd	SO0064241	NIJ	JMF104	STAKKAbox Quad Access Chamber 915x445x150mm BT Approved	1	0		IS
3	01/07/2022	J&A Groundworks Ltd	SO0064263	NG	6VCR350	600mm Vision 600 Chamber Restrictor Cap 350mm Opening	1	0		SD
4	01/07/2022	JD Excavations Tyrone Ltd	SO0064277	NIJ	6VCR350	600mm Vision 600 Chamber Restrictor Cap 350mm Opening	6	0		SD
5	01/07/2022	J&A Groundworks Ltd	SO0064263	NG	6VCA672	160mm Vision 600 chamber, outlet adaptor to 160mm UPVC – Socket	10	47		LS
6	01/07/2022	JD Excavations Tyrone Ltd	SO0064277	NIJ	6VCA12225	315mm Vision 600 chamber, outlet adaptor to 225mm TW – Socket (Level Invert)	1	0		NSI
7	01/07/2022	Flint Property Group Ltd	SO0064218	LK	4VCF280S	280mm PVCu Cover & Frame Square	3	0		LS
8	01/07/2022	Flint Property Group Ltd	SO0064218	LK	450VCFC	Polypropylene Cover & Frame: 450mm dia 50 kn Circular Locked	2	0		ONI
9	01/07/2022	J Holland Construction Ltd	SO0064286	NIJ	150TW90	150mm ID/ 178mm OD Twinwall Duct Bend 90deg Long Radius; P/E	7	0		ONI
10	01/07/2022	JD Excavations Tyrone Ltd	SO0064277	NIJ	106VR	250mm Spigot to 160mm Socket Reducer Level Invert	6	0		LS
11	04/07/2022	RM Contractors Ltd	SO0064328	NIJ	VJ4	Viking Johnson Maxi Fit 107-132mm Coupler Straight Coupler	2	0		LS
12	04/07/2022	JD Excavations Tyrone Ltd	SO0064387	NIJ	STOP300R	300mm Expanding Pipe Stopper 1" Test Thread Pressed Steel; 297-310mm range	3	0		LS
13	04/07/2022	Stave-Con Ltd	SO0064394	NIJ	450VCFS	Polypropylene Cover & Frame: 450 x 450mm 50 kn Square to Round	20	0		LS
14	04/07/2022	Stave-Con Ltd	SO0064394	NIJ	450VC4	450mm Inspection Chamber Base 110mm Inlet for installation up to 1.2m	2	0		ONI
15	04/07/2022	Barnwood Limited	SO0064408	NG	19004	AcoThreshold Channel End Plate 20mm long x 100mm deep x 61mm wide	4	0		ONI
16	04/07/2022	Hewlett & Sons Groundworks Ltd	SO0064375	NIJ	160TWBL	160mm OD TW Electrical Duct x50m Coil Black (140mm ID)	2	3		ONI
17	05/07/2022	O'Hagan Civils Ltd	SO0064478	LK	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	5	40		ONI
18	05/07/2022	Harkmac Construction Ltd	SO0064472	NIJ	4VPR	110mm Underground Drainage Rocker Pipe x600mm	42	0		SO
19	05/07/2022	Harkmac Construction Ltd	SO0064472	NIJ	450VCFC	Polypropylene Cover & Frame: 450mm dia 50 kn Circular Locked	20	0		ONI
20	05/07/2022	Harkmac Construction Ltd	SO0064472	NIJ	450VCFC	Polypropylene Cover & Frame: 450mm dia 50 kn Circular Locked	3	0		ONI
21	06/07/2022	Crossville Developments Ltd	SO0064516	LK	63PROEC	63mm Barrier Pipe End Plug; Type A (Fits both Protecta-Line & Puriton)	1	0		NSI
22	06/07/2022	L4 Civil Engineering Limited	SO0064552	NIJ	VFFAC1922	6" Clay/110mm Plastic, 170-192mm - 110-122mm Flexible Adaptor Coupling	7	0		SO
23	07/07/2022	O'Hagan Civils Ltd	SO0064761	LK	DUCT60	Naylor 600x600mm Duct Access Box 335mm Depth B125 Loading	7	29		SL
24	07/07/2022	Leidon Limited	SO0064698	NIJ	6VF20D	160mm PVC Drainage D/Socket Coupler	30	0		ONI
25	07/07/2022	Opel Construction Ltd	SO0064727	NG	6VCR350	600mm Vision 600 Chamber Restrictor Cap 350mm Opening	5	1		SO
26	11/07/2022	Galamast Ltd	SO0064907	NG	PIPE35	35mm Pipe Insulation 13mm Wall Thickness x2m	11	117		IS
27	11/07/2022	Connop & Son Ltd	SO0064846	LK	6VF90D	160mm UPVC Drainage D/Socket Bend 87.5deg	2	0		ONI
28	11/07/2022	Leidon Limited	SO0064826	NG	6VF20D	160mm PVC Drainage D/Socket Coupler	5	26		NSI
29	11/07/2022	Newtownstewart Construction UK Ltd	SO0065000	NG	31.6539	Frost PVC Spigot Adaptor BSP6 to160mm plastic (extends 150mm long.)	6	-1		FR
30	11/07/2022	JD Excavations Tyrone Ltd	SO0064917	NG	225R160	225mm Naylor TW Socket to 160mm UPVC Socket Level Invert Reducer	1	0		SO
31	12/07/2022	Leidon Limited	SO0064826	NG	10VF20D	250mm Underground Drainage D/Socket Coupler	2	0		LS
32	12/07/2022	Goldmax Ltd	SO0064801	LK	UWTSL150	Underground Warning Tape - Street Lighting Cable (x365m) - Yellow	2	0		ONI
33	12/07/2022	Foran Construction Ltd	SO0065127	NIJ	STOP1P	Inflatable Pipe Stopper Hand Pump - Schrader Valve	1	1		LS
34	12/07/2022	Umberslade Business Services Ltd	SO0065096	SB	96BTT	96mm Telecoms Duct Equal Swept 90deg Jct Grey (BT Type)	4	0		ONI
35	12/07/2022	Richard Hall Ltd	SO0065088	NIJ	6VF90D	160mm UPVC Drainage D/Socket Bend 87.5deg	2	0		NSI
36	12/07/2022	Entex Projects Ltd	SO0065044	LK	6VF90D	160mm PVC Drainage D/Socket Coupler	4	0
Jul

 

[gordsky]

Hi,
I ran your workbook with the code you supplied at top of page. the only change I made to your code was to remove the full stop before activecell and to dim row as long.
The code ran fine and gave me a time of 0.26 seconds. So the error must be within your workbook.

As you said earlier that removing the full stop after activecell gave you "Expected Function or Variable" then I can only assume that you have a sub or function with the same name somewhere along the line.

Some things you could try are to use 'MyRow' instead of row

MyRow = ActiveCell.Row

and

dim Myrow as long

or copy the data you have pasted above into a fresh workbook and paste your origional code (taking the full stop out and dim row as long) and see if that works

 

[Eric Penfold]

Thanks now sorted