Hope this makes sense as my searches have failed.
I would like to start my Match with an Offset of a named range then Autofill the column. The Autofill is retaining the original Offset and not progressing from what is "C2" to "C3" etc. Is this possible?
Line of code is
Only commitment is to the smartest quickest code & TIA.
Again, thanks!
Ron
I would like to start my Match with an Offset of a named range then Autofill the column. The Autofill is retaining the original Offset and not progressing from what is "C2" to "C3" etc. Is this possible?
Line of code is
VBA Code:
.FormulaR1C1 = "=IF(ISERROR(MATCH(OFFSET(ws_3Admin1_UserLogOn, 1,0), ws_3Admin_RngUserLogOn,0)),""Yes"", """")"
VBA Code:
Sub m_ws_3Admin1_IsClosed()
'
'== Other Range Names Used==
' ws_3Admin1_UserLogOn
' ws_3Admin1_rngUserLogOn
' ws_3Admin_rngUserLogOn
'====================
Dim LastCol As Integer
Dim LastRow As Long
Dim OneName As Name
Dim rngName As Name
Dim ThisWb As Workbook
Dim ThisWs As Worksheet
'
Set ThisWb = ActiveWorkbook
With ThisWb
ws_1Summary.Activate
Application.ScreenUpdating = True
ActiveWindow.SmallScroll down:=0
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'
ws_3Admin1.Activate
Set ThisWs = ws_3Admin1
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
'--Start---------------------
With ThisWs
'--Start Naming Ranges---------------
Range("A1").EntireColumn.Insert
Range("A1").Name = "ws_3Admin1_IsClosed"
Range("ws_3Admin1_IsClosed").Value = "Closed Account"
Range("ws_3Admin1_IsClosed").Offset(1, 0).Resize(LastRow).Name = "ws_3Admin1_RngIsClosed"
'--End Naming Ranges---------------
With Range("ws_3Admin1_IsClosed").Offset(1, 0)
.FormulaR1C1 = "=IF(ISERROR(MATCH(OFFSET(ws_3Admin1_UserLogOn, 1,0), ws_3Admin_RngUserLogOn,0)),""Yes"", """")"
.AutoFill Destination:=Range("ws_3Admin1_RngIsClosed"), Type:=xlFillDefault
.Calculate
' .Copy
' .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' .Application.CutCopyMode = False
End With
Range("ws_3Admin1_IsClosed").Columns.AutoFit
Range("A1").Activate
End With 'ThisWs
'--End--------------------------------------------------------------
ws_1Summary.Activate
Application.ScreenUpdating = True
If ws_3Admin1.Visible = False Then ws_3Admin1.Visible = True Else ws_3Admin1.Visible = True
Application.DisplayAlerts = True
Application.StatusBar = True
BeforeExit:
Application.OnTime Now + TimeSerial(0, 0, 0.1), "m_ClearStatusBar"
Application.StatusBar = "Done finding closed Admin Accounts."
Application.OnTime Now + TimeSerial(0, 0, 10), "m_ClearStatusBar"
End With 'ThisWb
End Sub
Again, thanks!
Ron