Hi Joel,

Many thanks. I keep getting "Run time error '1004' method 'run' of object
'_Application' failed" when it gets to the following code:

'Col P
LookupData = Application.Run("LOOKUP(" & .Range("C" & RowCount) & _
"'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,6,0))")
If IsError(LookupData) Then
.Range("P" & RowCount) = ""
Else
.Range("P" & RowCount) = LookupData
End If

'Col Q
LookupData = Application.Run("LOOKUP(" & .Range("C" & RowCount) & _
"'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$C," & _
"'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$H:$H)")
.Range("Q" & RowCount) = LookupData

Any ideas why?

Also it won't populate column O.

Regards

Andy

"Joel" wrote:

Change sheet name in 2nd line. I tried to get it right but there may be
errors. check carefully.

Sub GetData()

Set sht = Sheets("Sheet1")


With sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow

'column I
SearchData = .Range("G" & RowCount) & .Range("D" & RowCount) &
.Range("E" & RowCount)
Set c = Sheets("Lamps Table").Columns("D").Find(what:=SearchData, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("J" & RowCount) = ""
Else
.Range("J" & RowCount) = c.Offset(0, 1)
End If

'column K
Set c = Sheets("Lamp Watts").Columns("A") _
.Find(what:=.Range("J" & RowCount), _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("K" & RowCount) = ""
Else
.Range("K" & RowCount) = c.Offset(0, 1)
End If

'column L & M
If .Range("I" & RowCount) = "PN" Then
.Range("L" & RowCount) = (.Range("O" & RowCount) * 2335) / 1000
.Range("M" & RowCount) = .Range("L" & RowCount)
Else
.Range("L" & RowCount) = (.Range("O" & RowCount) * 4136) / 1000
.Range("M" & RowCount) = _
(.Range("L" & RowCount) / 2) + (0.07 * (.Range("L" & RowCount) /
2))
End If

'Col P
LookupData = Application.Run("LOOKUP(" & .Range("C" & RowCount) & _
"'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,6,0))")
If IsError(LookupData) Then
.Range("P" & RowCount) = ""
Else
.Range("P" & RowCount) = LookupData
End If

'Col Q
LookupData = Application.Run("LOOKUP(" & .Range("C" & RowCount) & _
"'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$C," & _
"'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$H:$H)")
.Range("Q" & RowCount) = LookupData

'Col R
If .Range("I" & RowCount) = "AN" Then
.Range("R" & RowCount) = (.Range("Q" & RowCount) * 4136) / 1000
Else
.Range("R" & RowCount) = (.Range("Q" & RowCount) * 2335) / 1000
End If

'Col S
If InStr(.Range("P" & RowCount), "SON") 0 Then
.Range("S" & RowCount) = "Compliant"
Else
.Range("S" & RowCount) = "Not Compliant"
End If

'Col T
If InStr(.Range("P" & RowCount), "PLL") 0 Then
.Range("T" & RowCount) = "Compliant"
Else
.Range("T" & RowCount) = "Not Compliant"
End If

'Col U
If InStr(.Range("P" & RowCount), "PLT") 0 Then
.Range("U" & RowCount) = "Compliant"
Else
.Range("U" & RowCount) = "Not Compliant"
End If

'Col V
If .Range("S" & RowCount) = "COMPLIANT" Then
.Range("V" & RowCount) = "COMPLIANT"
Else
If .Range("T" & RowCount) = "COMPLIANT" Then
.Range("V" & RowCount) = "COMPLIANT"
Else
.Range("V" & RowCount) = .Range("U" & RowCount)
End If
End If


'column I & O & W
If .Range("H" & RowCount) = "False" Then
If .Range("F" & RowCount) 2500 Then
.Range("I" & RowCount) = "AN"
.Range("O" & RowCount) = _
Application.Run("LOOKUP(" & .Range("I" & RowCount) & _
",'C:Desktop\[BBook.xls]Data'!$C:$C," & _
"'C:Desktop\[BBook.xls]Data'!$I:$I)")
Else
.Range("I" & RowCount) = "PN"
.Range("O" & RowCount) = .Range("K" & RowCount)
End If

.Range("W" & RowCount) = "NOT Compliant"

Else
Set c = Sheets("Parishes").Columns("A") _
.Find(what:=.Range("A" & RowCount), _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("I" & RowCount) = ""
Else
.Range("I" & RowCount) = c.Offset(0, 1)
End If

.Range("W" & RowCount) = .Range("V" & RowCount)
End If

'Col X
If .Range("I" & RowCount) = "COMPLIANT" Then
.Range("X" & RowCount) = .Range("R" & RowCount)
Else
.Range("X" & RowCount) = .Range("M" & RowCount)
End If

Next RowCount
End With
End Sub