I have the following code which is not giveing any result and not showing a fault. Can any one tell me what is wrong.
Two work sheets in the same book. One holding the "Information" And the Active sheet (as there will be more than one) Which needs data adding to it from the Information sheet.
The locals window give the expected results down to "ActWs.Range("L" & X).Value ="
Thanks in Advance
Sub test()
Dim ActWs As Worksheet, InfWs As Worksheet
Dim ActWsLastRow As Long, InfLastRow As Long, X As Long
Dim IndexRng As Range, MatchRng As Range
Set ActWs = ActiveSheet
Set InfWs = ThisWorkbook.Worksheets("Information")
ActWsLastRow = ActWs.Range("A" & Rows.Count).End(xlUp).Row
InfLastRow = InfWs.Range("A" & Rows.Count).End(xlUp).Row
Set IndexRng = InfWs.Range("B4:D" & InfLastRow)
Set MatchRng = InfWs.Range("A4:A" & InfLastRow)
'Set MatchRng = IndexRng.offset(0, 1)
For X = 4 To Range("A" & Rows.Count).End(xlUp).Row 'ActWsLastRow
On Error Resume Next
ActWs.Range("L" & X).Value = Application.WorksheetFunction.Index(IndexRng, _
Application.WorksheetFunction.Match(ActWs.Range("A" & X).Value.MatchRng, 0), 2)
Next X
End Sub
Two work sheets in the same book. One holding the "Information" And the Active sheet (as there will be more than one) Which needs data adding to it from the Information sheet.
The locals window give the expected results down to "ActWs.Range("L" & X).Value ="
Thanks in Advance
Sub test()
Dim ActWs As Worksheet, InfWs As Worksheet
Dim ActWsLastRow As Long, InfLastRow As Long, X As Long
Dim IndexRng As Range, MatchRng As Range
Set ActWs = ActiveSheet
Set InfWs = ThisWorkbook.Worksheets("Information")
ActWsLastRow = ActWs.Range("A" & Rows.Count).End(xlUp).Row
InfLastRow = InfWs.Range("A" & Rows.Count).End(xlUp).Row
Set IndexRng = InfWs.Range("B4:D" & InfLastRow)
Set MatchRng = InfWs.Range("A4:A" & InfLastRow)
'Set MatchRng = IndexRng.offset(0, 1)
For X = 4 To Range("A" & Rows.Count).End(xlUp).Row 'ActWsLastRow
On Error Resume Next
ActWs.Range("L" & X).Value = Application.WorksheetFunction.Index(IndexRng, _
Application.WorksheetFunction.Match(ActWs.Range("A" & X).Value.MatchRng, 0), 2)
Next X
End Sub