Eric Penfold
Active Member
- Joined
- Nov 19, 2021
- Messages
- 431
- Office Version
- 365
- Platform
- Windows
- Mobile
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?
Says Error "Method or Data Member not Found"
What is wrong with this part of code?
VBA Code:
Row = .ActiveCell.Row
VBA Code:
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
