I have a large worksheet (about 9,000 rows) with lots of vlookups to another sheet.
It is possible that some of the lookups will return '#N/A' but it is laborious to go through all the rows to check.
Is there a way to have a macro to look through that part of the worksheet, and to pop up a message box with the cell reference of the first error it finds? (I'm sure there is a way! I just dont know it)
I have a large spreadsheet that uses the lookup() to return data from two other large sheets.
The exact formula I am using is:
=IF(B1<>"",IF(ISERROR(MATCH(B1,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$A$1:$A$16374,0)),"NA",LOOKUP(B1,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$A$1:$A$16374,'X:Store Users Data FilesPMMJim.FosterDATA[shop sheet data.xls]shop sheet data'!$C$1:$C$16374)),"")
I have this same (or very similar) formula repeated about 3300 times in my main workbook. When I try to update links I get the following error. "Excel cannot complete this task with available resources. Choose less data or close other applications." If I "Open Source" it will update the links but when I close the "Source" I get the same error..."Excel cannot complete this task with available resources. Choose less data or close other applications." The source will close eventually and my links have updated but I can not save the work book. Much smaller versions of the same sheet work fine. What is the limit number of such links?
After dumping the Showcase Query result into an Excel file I am using a vlookup to insert data. My problem is that the vlookup won't find results unless I actually click on the cell that contains the lookup value to activate it. I have tried formatting the cells (both lookup and the return value) as general and text and nothing works (which makes my macro usless if it requires user intervention).
View 3 Replies View RelatedI have a list of last names in Sheet1!Column A. I want to see if any of those exist in Sheet2!Column A, and if it does I want to post the data from Sheet2!Column B into Sheet1!Column B.
View 2 Replies View RelatedAn offset sumproduct series of functions provides me the total score percentage for the top level categories. There are 5-6 in total and can easily be charted in a radar.
The second step is to maintain the 'dynamic aspect' (being able to delete rows) and lookup the high-level category, and the subsection and return the score for the individual question. This way I can create a chart for each of the 5 top level categories and show the survey score for each answer in the category. Note each question has a maximum score of 5 and are scored 1-5.
I have a few errors looking up values and percentages coming back as 500% with the addition of new columns (in red in attached). The first sheet is error free, the second sheet is the 'build'.
Note: Charts will be housed on a separate sheet.
have a formula or something along those lines that will look for the data you type in to the selected cell and will show you on how many workbooks it has been entered in. For example, I have workbook a, b, c, d, e, f. all have 12 sheets. On these 12 sheets there is a place to enter the serial number of an item. What I would like to be able to do is on a different workbook be able to type in a serial number and have it show which workbooks it has ever been typed in and its location on said workbooks. and if possible the results of the item which would be whether it was accepted or rejected( this bit is not totally must have. but the workbook and sheet location is a must.)
View 1 Replies View RelatedI have installed Excel on my new computer. Went to save a macro and I cannot locate the file "Personal.Xls"
Inside of Excel I can unhide "Personal.Xls" and it is visible.
We have an excel worksheet with textfields, that are compiled (when a btn is clicked) and the results are assigned to a static range. (Meaning, the results always appears in the same cells)
Question is: How can i have the results appear in a different row each time, starting on row 14 ?
I have one column of data (numbers). Within this column, are the values for two different categories...which are separated by a blank row. And then a total for each product given one column over; like this:
Product1 123
Product1 456
Product1 789 1368
Product2 456
Product2 123
Product2 456 1035
Either product can increase/decrease in rows, so I am unable to set a static range. The only solution I got on my own was to use SUMIF (sum the column of values if it matches the name "Product1" or "Product2"). This is fine until someone misspells the product name or leaves it off completely; which is why I prefer to go off the actual values.
My 1st preference is to go off the totals. Basically, return the 1st value (ie 1368) in the column, and then in another cell, return the 2nd value in the column (ie 1035).
My 2nd preference would be to sum the individual values in the column until a blank cell is encountered, and then return that value. Then continue on until another blank cell is encountered, and return that value as well.
I know very little of vba, so I haven't taken that route, but I bet this is easily done as a UDF (although I would prefer not to do it that way).
Is there a easy way to find the last row of data in an Excel sheet using VB?
View 2 Replies View RelatedI've got a formula that takes a value from the last numerical entry in a column. Is there a way to code this so that I don't have to enter the cell location of that number manually every time I append the sheet?
View 7 Replies View RelatedIn cells BS8 I have the following =100*SUBSTITUTE(BR16,"metres","") to get centremetres from cell BR8 and in cell BT8 I have the following
=IF(BS8,ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0),"") I get a #Value error
I have also tried
=ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0)
In BT8 but I get same error
I have used it in another sheetsheet and it works I don’t understand why is is happening ...
I am using a combination of validation, vlookup and simple formulas to lay out a workorder. If I leave something blank in one of the feilds then everything that comes after say #n/a and won't allow the calculation to complete. Is there a way I can set a default value or something so to make calculate all the feilds that ARE filled in?
View 9 Replies View RelatedFinding a link and deleting it. When I open a specific excel file I am working on I get an update links error
Unable to open [URL].... Cannot locate the Internet server or proxy server.
- I have done a search for "[" throughout the whole workbook and found nothing neighter within functions nor values
- I have looked at the Names and found nothing of the sort
- I have looked for hyperlinks within 2-3 images and found nothing
- I have looked through the VBA code
This does not happen when I open other excel files....
I'd like to use the INDEX & MATCH functions to look in a table (in 'misc') and find a value contained within a specific cell in Sheet 1 but I am getting a #REF error and I'm not sure why.The formula is as follows:
VB:
=INDEX(misc!1:50,(MATCH(Sheet1!A13,misc!D:D,0)))
Where:
Table ref = misc!1:50
Location of value to find = Sheet1!A13
Lookup array = D:D
I know how to delete duplicates using the function under the data tab, but how can i locate them. For instance, a spreadsheet with a list of PO numbers, how can i do a search to see if it is showing the same PO number multiple times?
View 9 Replies View RelatedIn the attached sample workbook, (Excel 2007 format), there are hundreds, maybe thousands, of drawing objects and rectangles. They are mostly located near the top of column AC. You can't see any of them, because they are empty, or formatted to have no line and no fill, ubt if you move the cursor around in that area, it will eventually give you the option to "select" one of the objects. They appear to be stacked on top of each other.
This bloats the worksheet terribly. I have managed to shave the size of the attached sample down to 100K, bu deleting about 200 of the objects, but if I save this balnk file as an Excel 2003 file, it is 1.3 Mb.
I have discovered how to show what objects are on the worksheet, by selecting "Find and Select" and choosing to show the "Selection Pane". This pane shows hundreds of blank objects in the sheet. When this pane is open, however, if I try to select and delete an object, Excel locks up on me.
I have the below formula that pastes data into the worksheet entered in the " " it works great but only when the workbook was last saved on that sheet, how can it locate that worksheet sheet even if the the workbook was last saved and another worksheet. if the workbook was saved on a different sheet name the vba doesn't finish
[Code].....
I'd like to find the first sheet in my workbook. I know how to find the last sheet:
View 4 Replies View RelatedI am having a problem with locating a certain date. What I am trying to do is scan down a list of dates and have returned the row is is in. I have been using the MATCH function and it was fine except when there is no MATCH. In the case where there is no match I would like it to select the next cell.
I am currently using this =MATCH(Search!G5,Data!F1:F10005,0) where Search!G5 is the date I want to find Data!F1:F10005 is the set of dates.
I have a workbook that requires a multiple of 3 to be entered in a cell. If a quantity other than a multiple of 3 is entered, a Msgbox pops up and requires the quantity to be changed to such value.
I am looking for where this is handled. I found part of it in a Private Sub:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
But I cannot locate where the other parts of the code is stored....
I would like to see how this is handled so I can incoporate this in my own worksheets.
This formula finds the last space from the right and returns the values less the last space and whatever is to the right-
=TRIM(IF(LEFT(Y25,2)="~C",REPLACE(LEFT(Y25,FIND("`",SUBSTITUTE(Y25," ","`",LEN(Y25)-LEN(SUBSTITUTE(Y25," ",""))))),1,FIND(" ",Y25),""),RIGHT(Y25,LEN(Y25)-FIND(" ",Y25))))
Results of a item of a whole size:
LAKAI KOSTON SLCT INDY SHOE BK
However- I forgot I had added a criteria to convert records that have a fractional value from decimal to a fractional value and an extra space shows up. I need to alter the above formula to handle this additional condition, returning the value w/ out the second space and the size 10 shown below.
Results of an item w/ a fractional size, ie. 10 1/2:
LAKAI KOSTON SLCT INDY SHOE BK 10
Linked to this post: new post started to help avoid confusion: but wanted to show prior material...
http://www.mrexcel.com/board2/viewto...766e4507d1a402
I am trying to append about 15 files of CSVs. I have code that works on importing the data, placing it at the end of the previous data, but then it clears the previous data. Here is the code
Sub import_BCDV()
Dim lastrow As String
Selection.End(xlDown).Select
Selection.End(xlUp).Select
' Range("A515").Select
lr = FindLastrow1()
lastrow = "A" & lr
MsgBox lastrow
Range(lastrow).Select
' "TEXT;J:QA ReportsQA ReportsWorkbenchBCVD 1-11-09 1-17-09.csv", _..........................
I have created a form that will be used my many people. The first person will enter their information and click a button to transfer their data to a second workbook, as well as send the workbook via email. At a later time, someone else will add more data to the emailed form and transfer this to the database in the second workbook. What I need is to be able to find the row in the second workbook containing the data transferred the first time and add the data that was put in later to the same row as the original.
View 7 Replies View RelatedHow can I program a variable 'rowcountnumber', that would count the number of active rows, starting from row 10 ?
View 8 Replies View Relatedtake a value from an input box and use it locate a record?
On my "Menu" sheet, the user will click a button which opens the input box and asks them enter a record number (format = PCAR.yy.nnn.xxxx)
The records are stored on a "Log" sheet and the lookup value is located in column "F".
I found code on another post which I used to create the input box, but don't know how to take this input and use it to find the relevant record.
I have the following code, which I use to find the first empty row in which to add a new record. if there is an error (record not found) to return to the input box to reenter the number.
Sub GetPCAR()
Dim Pcarnum
Pcarnum = InputBox("Please enter the PCAR number")
MsgBox ("Searching PCAR") & Pcarnum
End Sub
Sub AddPCAR()
Dim rNextCl As Range
Set rNextCl = Worksheets("PCAR Log").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Worksheets("PCAR Log").Activate
rNextCl.Select
ActiveWindow.DisplayHeadings = False
End Sub
I have a Workbook (only 1 sheet) that is filled out each month and consists of sales and revenue data. I need to copy the total sales and total revenue and paste them to another sheet. The only problem is that it is never consistent as to which cell the totals will be located in. They will always be in the same column, but almost never in the same row.
For example, one month there will be 8 individual sales and the total will be in Cell F13, but the next month will be F20. THe same needs to be done with Total Revenue (which is always one cell right of the Sales Total) I need to copy and paste the cell with Total in it (in this case F13) and do the same for each month after. This is not difficult to do in Excel, but i need some code to include this process in a macro.
Is there a way to reference a column depending on its header? Is there a way of doing this without the need of surveying one-by-one all the column headers with a for loop? I need this cuz if I have a very dinamic program which moves columns arround and depending on the state of the program the position of a column can change.
View 2 Replies View RelatedI've often lurked in the background here and have found great solutions to my problems from the ozgrid FAQ's and email outs.
I have a custom VBA formula that has a heap of ranges as inputs. I loop through one range then perform calculation on the other ranges in the formula.
The formula is to work on 2 sets of data, 1 with more columns than the other. I've set Optional variables for the unnecessary columns.
For some reason I keep getting circular references on the 2nd set of Data.
I've attached the spreadsheet (stripped down) to show both examples.
I'm absolutely confused why the "teaching" one calculates correctly and "support" gives a Circular reference error.
I've set the Iteration in options and this corrects it, however I'd like to know why one works perfect and the other fails?
I have a workbook with data tabs (one shown here) and a summary tab. Essentially, one inputs hours for people in the Data tab (Tech) that are then calculated/summed to total $ based on rates I have hidden elsewhere in the workbook (not included). I'm trying to bring the subtotal lines (highlighted green) into the Summary tab based on which subtotal and what month/year it is. My formula works fine, except for the fact that it is returning a #Value! due to the "Hrs" heading for each Phase. I don't know why it's doing this because I've not run into sum array issues when mixing numbers and letters before. The error formula is highlighted in yellow in my attached sample, but below is a copy of the formula.
View 10 Replies View Related