Excel Name Trick #10: Store Lookup Tables in Memory

I am using the VLookup function, which looks like this:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The column which I want match to my lookup_value is column A (= far to the left in the spreadsheet). But the column which I need to return is column CC (= far to the right). So I need to make a big table_array, spanning from A to CC and supply a col_index_num of 81.

Does this big table_array take up memory or in some way lower performance because of its size?

If so, is it better for me to make a copy of my A-column and place it at, say, column CB in order to make a smaller table_array?

I have Code that is making Ten Pivot tables for a branch in a new worksheet when I run it at first it makes the first nine Pivot Tables and on the last Pivot Table I get a runtime Error 1004 the Pivot table name is not Valid......
then i run it again then it give me a runtime error (some long number) then I run it again it gives me Run-time error 7 Run out of Memory which does not allow me to do any other branch. Here is the code:
Code:

Sub PIVOTRCHBEA()
'
' Macro4 Macro
' Macro recorded 6/10/2008 by Administrator
'
'
Sheets.Add
ActiveSheet.Name = "RCH&BEA PIVOT TABLES"
Sheets("RCH&BEA").Select
Range("B1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEA As PivotCache, ptRCHBEA As PivotTable

Set pcRCHBEA = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C2:R401C2")
Set ptRCHBEA = pcRCHBEA.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("A2"), _
   TableName:="RCH&BEA PIVOT TABLES", DefaultVersion:=xlPivotTableVersion10)
        
Worksheets("RCH&BEA PIVOT TABLES").Select
 With ptRCHBEA
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
Sheets("RCH&BEA").Select
Range("E1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAa As PivotCache, ptRCHBEAa As PivotTable
Set pcRCHBEAa = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C5:R401C5")
Set ptRCHBEAa = pcRCHBEAa.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("D2"), _
   TableName:="RCH&BEA PIVOT TABLEA", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAa
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
    Sheets("RCH&BEA").Select
Range("H1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAB As PivotCache, ptRCHBEAB As PivotTable
Set pcRCHBEAB = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C8:R401C8")
Set ptRCHBEAB = pcRCHBEAB.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("F2"), _
   TableName:="RCH&BEA PIVOT TABLEB", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAB
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
  Sheets("RCH&BEA").Select
Range("K1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAC As PivotCache, ptC As PivotTable
Set pcRCHBEAC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C11:R40C11")
Set ptRCHBEAC = pcRCHBEAC.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("H2"), _
   TableName:="RCH&BEA PIVOT TABLEC", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAC
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
      Sheets("RCH&BEA").Select
Range("N1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAD As PivotCache, ptRCHBEAD As PivotTable
Set pcRCHBEAD = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C14:R401C14")
Set ptRCHBEAD = pcRCHBEAD.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("J2"), _
   TableName:="RCH&BEA PIVOT TABLE D", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAD
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
    
      Sheets("RCH&BEA").Select
Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAE As PivotCache, ptRCHBEAE As PivotTable
Set pcRCHBEAE = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C17:R401C17")
Set ptRCHBEAE = pcRCHBEAE.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("L2"), _
   TableName:="RCH&BEA PIVOT TABLE E", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAE
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
        Sheets("RCH&BEA").Select
Range("T1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAF As PivotCache, ptRCHBEAF As PivotTable
Set pcRCHBEAF = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C20:R401C20")
Set ptRCHBEAF = pcRCHBEAF.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("N2"), _
   TableName:="RCH&BEA PIVOT TABLE F", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAF
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
            Sheets("RCH&BEA").Select
Range("W1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAG As PivotCache, ptRCHBEAG As PivotTable
Set pcRCHBEAG = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C23:R401C23")
Set ptRCHBEAG = pcRCHBEAG.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("P2"), _
   TableName:="RCH&BEA PIVOT TABLE G", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAG
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
                
                
Sheets("RCH&BEA").Select
Range("Z1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAH As PivotCache, ptRCHBEAH As PivotTable
Set pcRCHBEAH = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C26:R401C26")
Set ptRCHBEAH = pcRCHBEAH.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("R2"), _
   TableName:="RCH&BEA PIVOT TABLE H", DefaultVersion:=xlPivotTableVersion10)
        
With ptRCHBEAH
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
    
    Sheets("RCH&BEA").Select
Range("AC1").Select
    ActiveCell.FormulaR1C1 = "Date"
Dim pcRCHBEAI As PivotCache, ptRCHBEAI As PivotTable
Set pcRCHBEAI = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
         SourceData:="RCH&BEA!R1C32:R401C32")
 Set ptRCHBEAI = pcRCHBEAI.CreatePivotTable(TableDestination:=Worksheets("RCH&BEA PIVOT TABLES").Range("T2"), _
   TableName:="RCH&BEA PIVOT TABLE I", DefaultVersion:=xlPivotTableVersion10) 
        
With ptRCHBEAI
 Worksheets("RCH&BEA PIVOT TABLES").Select
      .AddDataField .PivotFields("Date"), "Count of Date", xlCount
      .AddFields RowFields:="Date"
    End With
 End Sub

The red text is where it stops

I have a macro that generates about 140 reports total, into 22 workbooks, in just over 7 minutes. It works well (would take me about 30 hours to do manually...) but it gradually takes my RAM down to almost nothing by the end; I have about 650MB of free RAM when I start. I believe it may be pivotcache related but I wanted to see what the experts might think is happening.

Process / situation description:
I have a data sheet, and a pivot table built on that data.

For each report, a true/false tag in the data sheet first gets updated based on some criteria, then the pivot table is refreshed so we can isolate and review the tagged data.

Then the pivot table data is analyzed - basic count and averages using range object variables and so on.

Results are then moved to a report template using variables - no copy/paste. The populated template gets copied to a new file and saved to a folder on my desktop. Each file created is only about 200 KB; it would be larger but I delete all names in the file that might have gotten moved over as well, before saving. Then a loop goes to the next criteria, updates the tag, refreshes the pivot table, etc.

Any thoughts on where the memory problems might be or how to address? I tried this after each pivot table refresh, but no improvement in RAM or performance.

[PT = pivot table object variable]
PT.SaveData = False

thanks,
Ken

Hi

I have this complex worksheets that I have been punching end of day quotes manually.

Now I have managed to link with internet for downloading the stock-quotes, and I would like to send that data from one sheet over to another to some of the cells when they match critereas that I have given, like the date of today and other.

I dont know how to explain this clearly, but if anyone knows how to give me a start it would be great.

I was wondering, is it possible to save data in a worksheet of an add-in workbook (.xla) and then have a function lookup data from that worksheet ?

I made the following custom function, where the user selects a zip code and the function looks it up in cells K4:K903 and then returns the corresponding zone from cells L4:L903. I'd like to save this workbook as an add-in, but I'm not sure how to indicate that I'd like the function to lookup the value from the add-in worksheet (if this is even possible) instead of the active worksheet?

below is the function.

Function ZoneLookup (OriginZip as string)

ZoneLookup = Application.VLookup(Left(OriginZip, 3), Range("K4:L903"), 2, False)

End function


I may also be going about this completely the wrong way, but if anyone has any ideas they would be greatly appreciated.

Thanks!

I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:

Code:

=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])

except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?

Good morning,

trying to find out what to do with my spreadsheet and the formulas in it.

If you look into the file 369. I can get to the stage where it outlines the mismatch figure. In the example it is 6 Mismatches.

But I cannot seem to get the table belwo right. Everytime I try and copy past the formula, the first set of data is the one from the first line and then I get a #NUM error, due to the fact that there seems to be a number reoccurence somewhere.

I took the youtube tutorial, but even copying that formula did not work.

My table consist of 2 sets of 600 values each.

Can someone tell me why I am getting this error and help me resolve the issue that the result is an existing set of data?

THANK YOU

BayerStars23
[IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot.png[/IMG][IMG]file:///C:/Users/BINGLE%7E1/AppData/Local/Temp/moz-screenshot-1.png[/IMG]

When you are making a formula say

=A5 + A6+A7

when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.

I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.

I can't figure it out!

Does anyone know what this is?

Please help!!

Thanks,
Michael

hi i have writtern the following in a comman button

sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub

I have in the range names of the month repeated randomly. June appears 6 times in the list

By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".

Hi guys,

I'm in the process of producing an excel markbook as I am a teacher.

I am trying to produce columns which when a percentage or mark for a test is added it is instantly converted to a grade from a lookup table in the same cell. I have tried various times but keep getting a circular reference error.

I can easily use lookup tables to place the grade in the adjacent cells but it becomes very untidy doing it this way and just want the grade to be viewable.

Is this possible to achieve and possibly conditionally format afterwards?


thanks