Hi Carim,
Would you please help me to run this macro with VBA code which is provided in the report attached to the facility index sheet. I have indicate the location in the attached file where I want to run this code.
Code
Option Explicit
Sub Results()
'Produce Results for Columns E,F,G,H,I
Dim c As Range
Dim rng As Range
Dim last1 As Long, last2 As Long
Dim i As Long, col As Long
last1 = Sheets("RetrievalReport").Cells(Rows.Count, "N").End(xlUp).Row
last2 = Sheets("Display").Cells(Rows.Count, "D").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For col = 31 To 32
Set rng = Sheets("Display").Range(Cells(2, col), Cells(last2, col))
For Each c In rng
i = c.Row
c = Evaluate("=INDEX(RetrievalReport!" & Col_Letter(col - 18) & "1:" & Col_Letter(col - 18) & last1 & ",MATCH(D" & i & "&B1" & "&C1" & "&D1" & ",RetrievalReport!N1:N" & last1 & "&RetrievalReport!C1:C" & last1 & "&RetrievalReport!D1:D" & last1 & "&RetrievalReport!E1:E" & last1 & ",0))")
c = IIf(IsError(c), "", c)
Next c
Next col
col = 33
Set rng = Sheets("Display").Range(Cells(2, col), Cells(last2, col))
For Each c In rng
i = c.Row
c = Evaluate("=INDEX(RetrievalReport!" & Col_Letter(col - 15) & "1:" & Col_Letter(col - 15) & last1 & ",MATCH(D" & i & "&B1" & "&C1" & "&D1" & ",RetrievalReport!N1:N" & last1 & "&RetrievalReport!C1:C" & last1 & "&RetrievalReport!D1:D" & last1 & "&RetrievalReport!E1:E" & last1 & ",0))")
c = IIf(IsError(c), "", c)
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
Display More