Multiple functions/formulas...

A

ajayb

Hi All,

I wonder if you can help with a huge problem? I have a worksheet with
64,519 rows and 24 columns (A-X). The workbook is currently 80MB in size
because the columns I-X have functions in. They are as follows:

Col I
=IF(H2="False",IF(F2>2500,"AN","PN"),IF(E2>7,"AN",IF(ISNA(VLOOKUP(A2,Parishes!A:B,2,0))," ",VLOOKUP(A2,Parishes!A:B,2,0))))

Col J =IF(ISNA(VLOOKUP(G2&D2&E2,'Lamps
Table'!D:E,2,0)),"",(VLOOKUP(G2&D2&E2,'Lamps Table'!D:E,2,0)))

Col K =IF(ISNA(VLOOKUP(J2,'Lamp Watts'!A:B,2)),"",(VLOOKUP(J2,'Lamp
Watts'!A:B,2)))

Col L =IF($I2="PN",(O2*2335)/1000,(O2*4136)/1000)

Col M =IF(I2="PN",L2,(L2/2)+((L2/2)*0.7))

Col N =M2*'C:\Desktop\[ABook.xls]Factors'!$B$1

Col O
=IF(H2="True",K2,LOOKUP(C2,'C:Desktop\[BBook.xls]Data'!$C:$C,'C:Desktop\[BBook.xls]Data'!$I:$I))

Col P
=IF(ISNA(VLOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,6,0)),"",(VLOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,5,0)))

Col Q =
=LOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$C,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$H:$H)

Col R =IF(I2="AN",(4136*Q2)/1000,(2335*Q2)/1000)

Col S =IF(ISNUMBER(SEARCH("SON",$P2,1)),"Compliant", "Not Compliant")

Col T =IF(ISNUMBER(SEARCH("PLL",$P2,1)),"Compliant", "Not Compliant")

Col U =IF(ISNUMBER(SEARCH("PLT",$P2,1)),"Compliant", "Not Compliant")

Col V
=IF(S2="COMPLIANT","COMPLIANT",IF(T2="COMPLIANT","COMPLIANT",IF(U2="COMPLIANT","COMPLIANT","NOT COMPLIANT")))

Col W =IF(H2="True",IF(V2="COMPLIANT","COMPLIANT"),"NOT Compliant")

Col X =IF(V2="COMPLIANT",R2,M2)

And obviously repeat for all the rows.

What I am after is some help in creating a macro that will work out the
calculations in each of the columns and paste the values in the cells. I
know I've seen a book do this before but I'm at a loss as to where to start.
Would really like it as simple as possible so that I can understand it and
take on board the knowledge.

Many thanks

Andy
 
J

Joel

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


ajayb said:
Hi All,

I wonder if you can help with a huge problem? I have a worksheet with
64,519 rows and 24 columns (A-X). The workbook is currently 80MB in size
because the columns I-X have functions in. They are as follows:

Col I
=IF(H2="False",IF(F2>2500,"AN","PN"),IF(E2>7,"AN",IF(ISNA(VLOOKUP(A2,Parishes!A:B,2,0))," ",VLOOKUP(A2,Parishes!A:B,2,0))))

Col J =IF(ISNA(VLOOKUP(G2&D2&E2,'Lamps
Table'!D:E,2,0)),"",(VLOOKUP(G2&D2&E2,'Lamps Table'!D:E,2,0)))

Col K =IF(ISNA(VLOOKUP(J2,'Lamp Watts'!A:B,2)),"",(VLOOKUP(J2,'Lamp
Watts'!A:B,2)))

Col L =IF($I2="PN",(O2*2335)/1000,(O2*4136)/1000)

Col M =IF(I2="PN",L2,(L2/2)+((L2/2)*0.7))

Col N =M2*'C:\Desktop\[ABook.xls]Factors'!$B$1

Col O
=IF(H2="True",K2,LOOKUP(C2,'C:Desktop\[BBook.xls]Data'!$C:$C,'C:Desktop\[BBook.xls]Data'!$I:$I))

Col P
=IF(ISNA(VLOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,6,0)),"",(VLOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$H,5,0)))

Col Q =
=LOOKUP(C2,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$C:$C,'C:Desktop\[CBook.xls]PFIEnergy7.rpt'!$H:$H)

Col R =IF(I2="AN",(4136*Q2)/1000,(2335*Q2)/1000)

Col S =IF(ISNUMBER(SEARCH("SON",$P2,1)),"Compliant", "Not Compliant")

Col T =IF(ISNUMBER(SEARCH("PLL",$P2,1)),"Compliant", "Not Compliant")

Col U =IF(ISNUMBER(SEARCH("PLT",$P2,1)),"Compliant", "Not Compliant")

Col V
=IF(S2="COMPLIANT","COMPLIANT",IF(T2="COMPLIANT","COMPLIANT",IF(U2="COMPLIANT","COMPLIANT","NOT COMPLIANT")))

Col W =IF(H2="True",IF(V2="COMPLIANT","COMPLIANT"),"NOT Compliant")

Col X =IF(V2="COMPLIANT",R2,M2)

And obviously repeat for all the rows.

What I am after is some help in creating a macro that will work out the
calculations in each of the columns and paste the values in the cells. I
know I've seen a book do this before but I'm at a loss as to where to start.
Would really like it as simple as possible so that I can understand it and
take on board the knowledge.

Many thanks

Andy
Click to expand...
 
D

Dave Peterson

I bet you're hoping that if you did all this stuff in a macro that the macro
would recalculate faster than the formulas and your workbook would be smaller.

My guess is that the converting the formulas to values would make the workbook
smaller, but using a macro would slow it down.

If I really didn't want to keep all those formulas...

I'd keep the formulas in row 2 and copy them down all 64k rows when I needed to
recalc. Then convert all the formulas in rows 3:65519 to values.

With that much data and that many formulas, I'd be very surprised if you ever
get it to be a mean lean spreadsheet.
 
A

ajayb

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF and OR Function 3
Sub runs, but gives error when called. 7
Remove Formulas then Delete Cells based on conditions - XL2003 1
Cross referencing with VLookup ? 10
SUMS 1
Calc Speed & Formula simplification 2
if statements depending on multiple VLOOKUP functions 1
isna, vlookup, range 13

Top