Slow Macro - Multiple Vlookups To External Sheets

Feb 29, 2008

I'm working on a macro which compares values in particular cells across different versions of a sheet. Unfortunately there are quite a lot of values, and my code is running very slowly.

I'm trying to get the data in a grid, with the file version across the top, and down the left the project name. For each file version I need to look up a specific value for every project listed.

At the moment, my macro is creating a seperate formula for each cell which does a vlookup on the project name to the specific external file.

The relevant bit of code is below

At the moment its runnig to slow by several orders of magnitude, Working on a 4 by 10 grid it takes about a minute, and I'm going to need it to be able to handle a 50 by 800 grid.

While the code is running, the CPU is not maxed out, so I'm assuming that its the calls to the file system which are taking all the time.

Application.ScreenUpdating = False

Dim ccount As Integer
Dim rcount As Integer
Dim sFilename As String
Dim sPath As String

With ActiveSheet
For ccount = 1 To Range("c1").End(xlToRight).Column - 2

sPath = Left(FileNamesList(ccount), InStr(FileNamesList(ccount), "Pipeline ~") - 1)

sFilename = Right(FileNamesList(ccount), Len(FileNamesList(ccount)) - InStr(FileNamesList(ccount), "Pipeline ~") + 1)

For rcount = 1 To Range("A65536").End(xlUp).Row - 2

by SDB.xls]Pipeline'!$A$1:$AO$300,3,0)

Cells(rcount + 2, ccount + 2) = "=VLOOKUP(""" & Cells(rcount + 2, 1) & """,'" & sPath & "[" & sFilename & "]Pipeline'!$A$1:$AO$300,3,0)"

Next rcount
Next ccount

End With

View 9 Replies


ADVERTISEMENT

Combine Sheets Using VLookups And Pivots?

Dec 8, 2013

I have 3 sheets in my workbook, DataA, DataB, and DataC

Data A contains:
ID_customer
and 4 variables A-D

DataB contains
ID_Customer
and 4 different variable W,X,Y,Z

Data C contains
ID_customer
and other 4 different variables

Request: I need to analyses the relationship between the variables using a Pivot table.

My thoughts so far: I think that I need to combine all the information onto one sheet first and then use the pivot table function, but its currently unable to combine onto one sheet, due to Id_ customer numbers appearing several times in DataB and DataC (and not always the same number of times in both), I need to retain all the information from DataB and DataC

I have attached a sample of my data : DataA.xlsx

View 6 Replies View Related

Vlookups With Multiple Returns

Aug 25, 2008

I am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.

View 13 Replies View Related

Using Average With Multiple VLookups

Nov 3, 2011

I have a table that has the data below

Month Jan Feb Mar Apr

Brand 0 1 2 3

I want to do a vlookup with match to find the month and then return the value of a rolling 3 months.. So if i look up Apr i want to look at the rolling 3 prior months average ie, average of (Jan,feb and March). i have the vlookup formulas working, its when i include the multiple vlookups (Match -1, -2 etc, the average is calculating the blank cells for some reason..

View 1 Replies View Related

VLookups With Multiple Results

Apr 12, 2013

I'm attempting to work with a v-lookup that will have multipe results. In column A of my spreadsheet are invoices numbers and in column B are account numbers. An invoice can have multiple accounts. For example, there are two lines for invoice "ABC", each with a different result in column B (see below)

Invoice Account
ABC Cash
ABC Receivable

My goal is to have the vlookup bring in both values, but in separate rows (see below)

Column A Column B Column C
ABC Cash Receivable

View 7 Replies View Related

Adding Multiple VLookups Together?

Jul 31, 2014

I'm having issues with a formula that I am using to add three vlookups together. The problem is that there may or may not be data in one of the columns I am looking up with the vlookup. I am looking up hold times for reps each week then adding them together to get a monthly sum. This formula works when data is available for all 4 weeks. When I have a rep that started in the middle of the month there isn't data for all 4 weeks so I want Excel to just treat that as a 0 or Null, Currently it just provides a 0.0 for the whole thing instead of adding what is there.

=(IFERROR(VLOOKUP([@Name],Sheet2!A:H,2,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,4,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,6,0),0))+(IFERROR(VLOOKUP([@Name],Sheet2!A:H,8,0),0))

View 9 Replies View Related

Multiple Vlookups In One Cell

Aug 16, 2009

I have two lists of IDs in 2 columns. I want to sort of do a vlookup of each row in one column to see if it is in the next column, and I want to sum a third column that goes along with column 2 based on whether the same IDs from column one is in the second column. And I want to do all this in one cell. For example:
A B C
TestA TestZ 10
TestB TestY 20
TestC TestB 8
TestD TestA 7

I need one cell to calculate for me that based on the IDs in column A, column B matches with the IDs TestB and TestA, therefore giving me the sum of 15 (8+7). Doing a vlookup and auto-filling down and then summing up is not an option, so I'm trying to calculate it within one cell if possible. And I can't get sumproduct to work because the matching IDs are not necessarily in the same rows.

View 9 Replies View Related

Vlookups That Return Multiple Values?

Dec 17, 2013

I am trying to write a formula that is based on a vlookup that will return multiple values. Attached is a spreadsheet that is an example for what I am trying to do. On the download tab it is an example of a general ledger. On the total tab I want to enter a GL# and have it list all the entries for that GL number. I know I can try an array, but I have not been able to figure that out.

View 6 Replies View Related

Faster Way Of Doing VLOOKUPs On Multiple Items?

Nov 9, 2009

Instead of doing a long long formula that is =VLOOKUP(x1,....)+VLOOKUP(x2,.....) is there a faster way of doing it in the one formula?

View 7 Replies View Related

Combine Multiple Vlookups In One Formula

Sep 20, 2008

I am using this formula
=IF(SEARCH("pipe",A2),VLOOKUP(C2,Sheet2!A4:B12,2))
to look up a reference code for "pipe". The reference codes for "pipe" are in column 2 of a table on sheet 2 and are dependant on the dimensions of the pipe given in column 1 of the same table. C2 in the formula is where the dimensions are given. (hope that's clear ).

What I want to do is to extend the above formula to look up column 3 of the table on sheet 2 if, for example, A2 contains "Bend", i.e., =IF(SEARCH("Bend",A2),VLOOKUP(C2,Sheet2!A4:B12,3)).

Combining those two formula using "=if(and " worked for me for the first test, but changing the contents of A2 from "pipe" to "Bend" results in a #Value type error.

I can't use if A2 = "pipe" because extra words could be introduced into A2, e.g., "copper pipe" - therefore the equals would not be appropriate.

View 9 Replies View Related

ISERROR Formula In Cell That Has Multiple Vlookups?

Apr 18, 2008

My problem is when i get a product that isnt in the reference sheets (ie. in cell I19), so when that happens i get "#N/A". I know i can add in ISERROR into my formula, but every time i've tried this it hasn't worked so im missing something...

View 9 Replies View Related

Excel 2007 :: Using VLOOKUP And OFFSET Instead Of Multiple IF And VLOOKUPs?

Jul 29, 2014

Sample data

2014
2013
2012

Country A

2%
3%

Country B
5%
6%
7%

[code].....

Now, whenever I type in any country it should show me the most recent figure available for that country.

Suppose, if I type Country A in, say cell A14, then it should give me output 2% in cell, say A15. When I change that same cell to country C, it should give me output 10%, country B should give me output 5%.

Currently I have used IF and multiple/nested VLOOKUPs to arrive at this output. Is there any better way to do this with VLOOKUP and OFFSET?

I am using Excel 2007.

View 3 Replies View Related

Macro - Tables Invisible To VLookups

Nov 14, 2012

I created a macro in an Excel workbook which uses MS Query to return 2 tables of data, and insert them into the workbook. Then, another macro uses a VLOOKUP to enter the values from the tables into their respective rows in other worksheets. Everything worked like a charm, but when I duplicated the workbook (copy and paste) in order to use it for other companies, the VLOOKUPs now return an error (only in the dupe workbooks; the original still works fine). After troubleshooting the issue, I determined that the VLOOKUPs cannot "see" the tables - almost as if they have become invisible. I even tried deleting the query macro from the dupe books and recreating it, but still the data is invisible to my VLOOKUPs.

View 3 Replies View Related

Macro To Run Vlookups On Pivot Table Results

Jul 14, 2014

I'm trying to write a macro that will look up information about clients that result from a pivot table. Depending on what two inputs are put into the pivot table (coverage, existing or target company) the client names that the vlookup needs to reference will change.

The vlookup information for each client would paste into a different cell on another worksheet, and change depending on which clients came up in the pivot table. So the macro would need to look up clients from a specified range that the pivot table is in.

View 1 Replies View Related

Macro To Run Vlookups On Pivot Table Results?

Jul 14, 2014

I'm trying to write a macro that will look up information about clients that result from a pivot table. Depending on what two inputs are put into the pivot table (coverage, existing or target company) the client names that the vlookup needs to reference will change.

View 1 Replies View Related

Excel 2010 :: Macro To Save Multiple Sheets To Multiple PDF With Cell Value As Filename

May 6, 2014

I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.

I am using Excel 2010.

View 5 Replies View Related

Select External Workbook And Them Merge All Sheets In One

Jul 22, 2009

I'm trying to create a workbook that opens the windows explorer and let me select a workbook, then in one worksheet merge all the active sheets present in one.

View 2 Replies View Related

Update External Sheets Without Opening Workbook

May 2, 2009

I have 3 sheets: 1: master entry sheet that fills data in 5 different excel docs. Then there is another doc that aggregates data from the 5 and presents totals on the data. I am trying to get the end result without having to have 7 different excel files open, but when I enter into the master entry sheet, the data doesn't seem to be "pushed" up to the 5 different docs until each one is opened up. Obviously, the master aggregator is not updated since the 5 don't have the new data. When the files are all opened at the same time, the data flows perfectly, I'm just trying to find a way to avoid having to open the 5 docs in the middle of the process.

View 9 Replies View Related

VBA Access Common Cell In Many Different Sheets In An External Workbook

Oct 21, 2008

Using Microsoft Office Excel 2003 (11.8220.8221) SP3, I started with a sheet, Sheet7, that pulled the first non-blank error message found in a common cell (i.e., A7) in Sheet3.1, Sheet3.2, ... , Sheet3.15, Sheet3.16. All of these sheets resided in a single workbook, and worked correctly using the following VBA code snippet:

View 5 Replies View Related

Disable Automatic Absolute References When Linking To External Sheets?

May 30, 2014

Would be a massive time saver but can't find anything here or on Google on how to do this.

View 1 Replies View Related

Hiding Sheets Causing A Runtime Error On External Data Refresh

Apr 20, 2007

I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.

View 4 Replies View Related

Slow Running Macro

Oct 3, 2007

Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.

The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.

View 9 Replies View Related

Slow Delete Row Macro

Mar 9, 2009

I have got the macro below, my problem is that when you run it, it takes 20 seconds or so. In my other larger spreadsheet it was almost instant.

View 4 Replies View Related

Macro Running Very Slow

Jun 11, 2009

I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?

View 7 Replies View Related

Macro Runs Slow

Nov 13, 2008

The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()

Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................

View 9 Replies View Related

Slow Macro Loop

Jan 10, 2007

Need some alternative code that would speed the execution of this macro. My weak attempt runs noticeably SLOW.

Dim cell As Range
For Each cell In Range("TPD")
If IsNumeric(cell) And Not IsEmpty(cell) Then
If cell.Value > 0 Then
cell.EntireColumn.Hidden = False
Else
cell.EntireColumn.Hidden = True
End If
End If
On Error Goto 0
Next
End Sub

View 2 Replies View Related

Macro Running Slow

Jan 30, 2007

I have a macro that isn't very complex but it is running very slow, does anyone know any tricks on how to speed it up?

View 9 Replies View Related

Run VB Macro On Multiple Sheets

Apr 17, 2009

Hi i have this macro below which works perfectly to clear the contents of column A. I will have multiple worksheets all named from 1,2,3,4,5 upto 50.

What i would like to do is modify this script below so it will run on the worksheets labelled above.

View 7 Replies View Related

Run Same Macro On Multiple Sheets?

Sep 8, 2012

I have a macro and I want to run that macro on multiple sheets. I don't know how to select multiple sheets. I have selected for example three worksheets.

I have used the following code but the code runs only on sheet 10. what do I need to do to make it work on all the sheets ?

Code:
Sub multiplemacro()
Dim wsh As Worksheet
Sheets(Array("Sheet10", "Sheet11", "Sheet12")).Select

[Code].....

View 3 Replies View Related

After Printing, The Macro Works Very Slow

Apr 5, 2007

I programmed a simple macro (attached to a button) that scan the lines in the sheet and uses the function: Cells(y,2).EntireRow.Hide=TRUE (in order to hide certain lines). Usually, It runs fast without problem. But when I print out an area of the sheet, it cause the macro to slow down substantially. (when it runs again). In order to fix that , I had to close the file and reopen it again ( and not print out)

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved