ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Multiple functions/formulas... (https://www.excelbanter.com/excel-programming/416403-multiple-functions-formulas.html)

ajayb
Multiple functions/formulas...
 
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(F22500,"AN","PN"),IF(E27,"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:Desk top\[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

joel
Multiple functions/formulas...
 
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" wrote:

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(F22500,"AN","PN"),IF(E27,"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:Desk top\[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

Dave Peterson
Multiple functions/formulas...
 
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.


ajayb wrote:

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:

<<snipped

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

--

Dave Peterson

ajayb
Multiple functions/formulas...
 
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


All times are GMT +1. The time now is 03:00 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com