Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Multiple functions/formulas...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default 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



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple functions, conditional functions HeatherBelle Excel Worksheet Functions 7 October 17th 08 03:57 PM
multiple formulas and functions volunteer Excel Discussion (Misc queries) 1 June 9th 08 09:25 AM
Functions/Formulas to count multiple variables 2many#s Excel Worksheet Functions 7 June 20th 07 05:20 AM
Index & Match functions - multiple criteria and multiple results [email protected] Excel Worksheet Functions 4 May 2nd 07 03:13 AM
Formulas for multiple functions Jerry Foley Excel Programming 3 February 19th 07 11:33 PM


All times are GMT +1. The time now is 06:10 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement