Delete One Of The Source Rows I Get A #REF Error
Aug 17, 2007
I have two worksheets that directly link a range of cells on both sheets, ie: Output!a1 = Database!a1.
In this case, the Database sheet contains data that I want to manipulate by deleting entries that are defined in rows. however, when I delete one of the source rows I get a #REF error on the Output sheet.
Is there any way to avoid this so the entries will automatically shift down a row if a row has been deleted? :o
So if i were to delete row two on the source page and the data from row three moves down, that would, in turn, cause the row three data on the output page to move down one row as well.
View 9 Replies
ADVERTISEMENT
Apr 29, 2008
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
View 2 Replies
View Related
Sep 11, 2007
After performing several operations and sorting the results I end up with a lot if #VALUE! errors in colum B. I would like to search thru those results and delete any row containg "VALUE! in colum B
View 2 Replies
View Related
Dec 31, 2012
I have some code that is working great except that it throws up an error on the last run through because it doesn't find any more rows to delete. I've tried "On Error GoTo" but it doesn't catch it. It seemed to work on my home computer which is running Excel 2010 but then on Excel 2007 I get the debug screen and I don't want that to show up for other users that I share the code with.
Sub DeleteHeaders()
Dim lastrow As Integer
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
[Code]....
View 3 Replies
View Related
Feb 15, 2008
I have a listbox which brings up my bets for todays horseracing. If say one of my selections is a non-runner, would I be able to add a command button to delete that selection from the source data.
The source range is "AL10:AN24". So all I would want to do is select the selection if it isn't running and click on the command button to delete the selection.
I already have code to sort the source data and would like to add this code to the new code, so once a selection has been deleted the source is also resorted.
Here is that code, in case it is needed:
Private Sub CommandButton1_Click()
Range("SYSTEMS!AL10:AN24").Sort Key1:=Range("SYSTEMS!AL27:AO41"), Order1:=xlAscending
End Sub
View 9 Replies
View Related
Dec 30, 2007
I'm compiling statistics from a number of separate workbooks. When I (or more to the point - one of my staff) open the Stats Workbook the formulas show #VALUE unless the other Workbooks are opened. I want the Stats to calculate without the user having to open the other workbooks as most users won't know which ones to open.
I could generate VBA code when the workbook opens to also open the other ones but wondered if there was another solution.
View 9 Replies
View Related
Apr 13, 2007
When I run it the message "could not detect range" comes up. I selected my desired range then ran the macro and did not get the desired results. It looked like maybe only one row was deleted. My data has seemingly random blank rows between it, some small row ranges, some very large.
View 2 Replies
View Related
Apr 28, 2014
I am currently creating a form to hold 2 drop down lists that change depending on the selection in the first one. I had never done this before and taught myself and got it to work minus one problem. As soon as I input the formula under the Data Validation the error: "The Source currently evaluates to an error. Do you want to continue?" pops up. Right now the formula works except for the last 6 options in my first drop down. When I select them nothing pops up in the 2nd drop down menu. I have double checked and triple checked that I have no spaces or anything in my LIST on worksheet 2 or any of the names are input correctly.
A7 is the first drop down - PROPOSED
B7 the second. - ITEM #
Lists and data to populate the lists are on worksheet 2 with the "List" at the bottom of worksheet 2...
View 1 Replies
View Related
Oct 26, 2008
why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.
Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. The workbook in question is attached.
View 14 Replies
View Related
Jul 29, 2013
I'm trying to create a userform with a combobox wherein items that will be provided in it will be based on the worksheet named NA. When i try to change the Rowsource on the properties of the combobox i'm getting an error "could not set the rowsource property. Invalid property value". Do I need to declare the name of the worksheet before changing the rowsource?
View 6 Replies
View Related
Nov 30, 2007
I am linking two worksheets together. Formulas work fine except when I close the source sheet, it turnes into #REF error. I saw in one of the posts that you can not use INDIRECT formula on closed workbooks (INDIRECT is a part of the formula in my sheet) and downloaded the changingExternal. zip file as directed. Although, after staring at it for a while, I realized that I had no idea as to what was going on in there.
View 3 Replies
View Related
Sep 19, 2012
i have code like this to copy certain word in cell and place it at new sheet:
VB:
Sub Foo()Dim i As Long, iMatches As Long
Dim aTokens() As String: aTokens = Split("Accommodation", ",")
For Each cell In Sheets("Sheet1").Range("C:C")
[Code].....
the problem is the cell source is not deleted. what line should i change in code above to move the result to new sheet and delete row source
View 7 Replies
View Related
Jan 25, 2013
What is causing this to error. Basically I'm just trying to highlight cells, and create a pivot table out of that. Here's my Code:
Sub Macro1()
'
' Macro1 Macro
Dim myRange As String
myRange = "Sheet1" & Selection.Address
Sheets.Add
[Code] ..........
When I debug, the value of myRange is something like Sheet1$A$19:$K$37 with the actual cell values being whatever I highlighted. I get a Runtime Error 5 Invalid procedure call or argument. I'm assuming it's something with my sourcedata variable, but I'm not sure what.
Also, I did try commenting out the Sheet.Add and Sheets("Sheet2").Select Cells(3, 1).Select lines, but it still produced the same results.
View 9 Replies
View Related
Nov 17, 2011
I am working with Excel 2010.
There are certain .xlsm files in our system (some have macros and others don't) which generate "Error: Source not Found" in the Edit Links dialog box when the Check Status button is pushed. However, the source location is correct, as you can click on Open Source and it will open the correct file.
For simplicity of explaining my issue, consider the following scenario. In my workbook "Corn Production Summary.xls_" I link to 2 source files: "Iowa.xlsm" and "Nebraska.xlsx" (Note: The extension of the summary file doesn't matter. This error happens regardless of what the summary is.)
When I open "Corn Production Summary" I am prompted to update values. I select Update and the file refreshes and recalculates appearing to have updated all values (i.e. no error messages). However, the values from Iowa.xlsm did NOT update and there was no error message indicating that the values didn't update.
To confirm this, I go to Data>Edit Links>Check Status in "Corn Production Summary" and I see that Iowa.xlsm has the "Error: Source not Found" error. I click on Open Source and once the file is open, my data in the summary file will update.
One work around was to do the following process (with the summary file closed):
1) Iowa.xlsm and Save As Iowa.xlsx (removing macros if needed)
2) Save As Iowa.xlsx as Iowa.xlsm (overwriting the file that is currently there and readding the macros)
3) Open the summary file and both Iowa and Nebraska work just fine now.
There are a lot of files like this, and because Excel doesn't prompt us that it isn't really getting the values from these certain .xlsm files we would have to search in "Edit Links" for each file to make sure that they really are updating.
View 3 Replies
View Related
Feb 9, 2012
I am trying to create a simple xy graph in Excel 2010 using dynamic source data.
I have data in three columns.
There are headings on line 2 and the data starts on line 3.
A B C
1.
2. dp ss ff
3 .1 .2 .3
4 .3 .4 .4
5 .4 .8 .7
6 .1 .6 .8
7 .3 .5 .1
8 .5 .4 .4
I want to create an XY chart with C3 to C8 as the X axis (or dynamic to what ever the last data point is) with A3 to A8 as the Y axis (or dynamic to what ever the last data point is).
I have Defined names for C3 to C8 (myXvalues) and A3 to A8 (myYvalues).
I have created a simple XY graph and selected the ranges as normal.
This is =DATA(,Data!$C$3:$C$9,Data!$A$3:$A$9,1)
The page is named DATA
What I am trying to do now is to edit this range to make the range dynamic.Unfortunately I get error messages which ever way I try to edit the range.
I have tried:-
=SERIES('Data'!$c$3,'Data'!myXvalues,'Data'!$A$3,'Data'!myYvalues,1)
=SERIES('Data','Data'!myXvalues, 'Data'!myYvalues,1)
=SERIES(,Data!myXvalues,Data!$myYvalues,1)
=SERIES(Data!,Data!myXvalues,Data!myYvalues,1)
The data range can go from 8 to 30000 hence the need for a dynamic range!
PS: Also, is there any way to create this graph and dynamic source data in VBA. I have tried this with no success.
View 2 Replies
View Related
Nov 14, 2012
I am trying to adapt a macro to run on data that is consistently in the same format but the data does change, the macro needs to run over and over on new excel workbooks, dynamic range ? instead of a set source ?
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"weekly71012!R1C1:R9379C30", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
I am sure this is the problem as i initially created the macro using weekly71012 excel file however i just want it to run on every file i pull it off?
View 2 Replies
View Related
Oct 2, 2008
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous
View 2 Replies
View Related
Dec 24, 2007
I'm trying to put a search userform in a worksheet. I got this code from the web and tried to alter it to fit my worksheet....
View 7 Replies
View Related
Jul 4, 2014
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------
UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
View 6 Replies
View Related
May 21, 2007
I have embeded a PDF file in an Excel Worksheet. When I double-click to open the PDF file the I get an error message: "Cannot start the source application for this object." However, the pdf file opens anyway. how to prevent the error message from displaying (without stopping real error messages)? (...happens in Excel 2000 and Excel 2003) NB: I researched this site first - user "Ed" raised the same issue 27/1/03 - but it doesn't appear to have been resloved.
View 4 Replies
View Related
Dec 16, 2009
I received the following data validation error: Source evaluates to an error. Do you wish to continue? I received this error after attempting to reference a named range on another workbook.
Steps I took:
1) Named the range E5:E123 on the set-up workbook Students8
2) In the Reading8 workbook, I created a name. I called it names and I had it reference =set-up.xls!Students8
3) In the cell, I selected Data-Validation. Allow--List Source =Names
View 2 Replies
View Related
Jul 15, 2009
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
View 3 Replies
View Related
Feb 26, 2014
I have 5 pivot tables on 5sheets, all looking at the same source data in sheet 6
On the source data there is a filter on the headers, if you change the filter, is it possible for all 5 pivot tables to update according to the filter?
My starting point is the below, but there probably is a better way but i would want the sourcedata to equal visible rows on the source data sheet headers run A:K and up to row 10000 .
View 2 Replies
View Related
Apr 1, 2009
Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?
View 5 Replies
View Related
Jun 9, 2009
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
View 9 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
View Related
Apr 3, 2014
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
View 14 Replies
View Related
May 22, 2008
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies
View Related
