Find A Variable Through A Source Range

Apr 19, 2006

I’m using these two codes to find a variable thru a source range. Everything looks to be working well, but as soon the macro tries to copy the returned range I got the following message. Run-time error ‘1004’: Copy method of Range class failed

However, when I press the end button in the error window, the returned range is copied into the target range. Does anybody know how to avoid the error message? Also, is there any way to pass the returned range to an array?

Note:
The source range is 40,000 rows by 14 columns
The returned range is 163 rows by 14 columns.

Sub SearchRangeOne()
Dim SourceRange As Range
Dim TargetRange As Range
Application. ScreenUpdating = False
Set SourceRange = Worksheets("Actual").Range("Actual_Key")

If Not Find_Range(Range("A1").Value, SourceRange, xlValues, xlPart) Is Nothing Then.............

View 8 Replies


ADVERTISEMENT

Excel 2007 :: Find Source Data Range Of Pivot Table?

Feb 4, 2013

how to find the source data range of a pivot table (that already exists) in Excel 2007?

View 1 Replies View Related

Find Max Value In A Changing (variable Range)

May 11, 2009

I am trying to find the max value within a range (14 cells) contained in one column ("C" in this case). This range will change corresponding to the current cell. Ex. as the current cell moves down (or increases in number) the range of cells to find the Max in moves down (or increases in number) as well. The Max value will then be used as a variable in a equation but for now just getting it into a cell is fine. This seems like it would be easy but it is driving me absolutely batty!

I currently have the non-working code located in a For loop that contains other calculations as well. I've taken those calculations out since they work fine but cloud the bad part. So far I have tried:

View 3 Replies View Related

Inserting VLookup With Variable Source Name With VBA?

Feb 23, 2012

I'm trying to create a macro that will open automatically generated report outputs and manipulate the data.

The macro will run from it's own workbook (Report Macro.xls), sheets / cells within this workbook are available as 'helpers'.

So far what I have working ok is;

Via a browse dialogs in a userform in Report Macro.xls I select 5 different report outputs. I'm storing the full filename & path in Sheet Data, Cells B1:B6 (B1 being the filename & path to the macro file). In column C I have just the filename

The first file is opened and manipulated as required. The next thing I need to do with it is to open the second file (filename / path in B3) and put a Vlookup in cell 02 of the first file which will look for data in the second (the second file will only ever have a single sheet).

As a formula I'd be using

Code:
=VLOOKUP(A2,'Email 22FEB12.XLS'!$B$7:$C$13120,2,FALSE)
In VBA if I use;

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'Email 22FEB12.XLS'!R7C2:R13120C3,2,FALSE)"
It works great. However, the second filename will change and I need to take it from the macro file, sheet 'Data', cell 'C3'.

How can I use the contents of sheet Data, cell C3 in the VBA code above instead of the filename being hard-coded?

View 1 Replies View Related

Define Pivot Table Source As Variable

Dec 28, 2007

I am producing graphs that compare last years figures (static) with this years figures ( Dynamic) and this years figures come from a Pivot table and Worksheet that has been imported. The value returned in each cell for the graph's dataset is derived from a specific Pivot table row and the column name (month) that matches the column of the data set - so far so good. The formula currently looks like this.

=IF(ISNUMBER(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),0)

where 'Count PS' is the name of the worksheet and Q3 is the reference cell that holds the Month Name. The problem is that I will not always be creating the report and other users may use a different name for the imported worksheet. I want to be able to set the name of the worksheet to be referenced in a supporting worksheet so that the user could simply amend the cell value to be the name of the source worksheet, but I can't work out the syntax

View 2 Replies View Related

Find And Find Again - Object Variable Or With Block Variable Not Set

Jun 5, 2007

After doing a search, I need to ask an Excel 2003 question. I'm creating a tool to open three sparate workbooks. One is the driver and I need to use it to pull data from the second and then to place that data along with additional data into the third.

My driver data may consist of a single value or multiple values separated by semi-colons in a cell. A single value works fine. The first value in a multi-value condition works fine. the second find, however, gives me an object error. I'm using the " split" verb to separate the values. Here's my code. Have you any idea why the second find is throwing up this error when the first find works correctly?

varData = Split(strRef, ";", -1)
For J = 0 To UBound(varData)

varSrchVlu = Trim(varData(J))
' do the ARIS Exrtact matching
Workbooks(strManualFile).Activate
Workbooks(strARISExtract).Activate ' Activate ARIS Extract
Sheets("Processes").Cells(2, 1).Activate
Workbooks(strARISExtract).Sheets("Processes").Range("A2").Select
Workbooks(strARISExtract).Sheets("Processes").Columns("A:A").Select

View 3 Replies View Related

Find Method Code: Object Variable Or With Block Variable Not Set

Sep 8, 2006

I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow

Here is my

Sub test()

Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long

'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'for anyof the two lines down I get the message "object variable OR block variable not set"

lRow = rFoundCell.Row
lCol = rFoundCell.Column

End Sub

View 5 Replies View Related

Find Dates Macro: Object Variable With Block Variable Not Set

Nov 21, 2006

I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select

End Sub

View 9 Replies View Related

Find Method: Object Variable Or With Block Variable Not Set

Aug 28, 2006

I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....

View 7 Replies View Related

Find Source Of A Pivot Table

Jul 18, 2007

How can I possibly find the source path of a file used to build a pivot table. So basically the pivot table is using an external source and I'm unable to find it.

View 2 Replies View Related

Find Out The Source Data From A Pivot Table?

May 10, 2009

I've got a bit of a problem revealing pivot table source data, so just wondering if anyone know how to reveal it? My colleague sent me the spreadsheet, I can see the pivot table, but I can't find the source data that the pivot table links to.

View 3 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View 5 Replies View Related

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this

View 9 Replies View Related

Hyperlink From Range Source

Feb 1, 2008

I store Function in a table such as:
=HYPERLINK[url]

But when I connect to the data source and display it - it actually displays:
"=HYPERLINK[url]

Instead, I want it to actually be the Excel function and only the work
"Google" would show up as a hyperlink.

What I'm REALLY trying to do - is by showing a data source, the user sees a hyperlink column that takes them somewhere. I have seen other posts that talk of using VLOOKUP and Hyperlink together to acheive this but I cannot figure it out - I need a sample pulling from a data source.

View 7 Replies View Related

Chart Without A Specific Source Range

Sep 1, 2009

I wrote a macro to create a chart out of a specific range of data.

View 11 Replies View Related

Define Chart Source Range

Jul 22, 2006

How can I take the source data of a chart to be all those cells in the column, that begin with the number "1"?

View 2 Replies View Related

Change Chart Source Range

Jun 14, 2007

My spreadsheet has multiple charts, each referencing a dynamic named range as its source data (e.g. "California1", "California2", etc). Each named range contains multiple series. I would like to make it easy for a single chart to quickly adjust to display different named ranges (e.g. view the chart with "California1" data, then update the same chart to view "California2" data).

I'd like to do this without having to right-click on the chart, select Source Data, and then type in "California2" as the Data Range. My question: is there a way to make the chart's Source Data Range dynamic so that I can quickly select the named range that I want to display? For example, would it be possible to tie the Data Range to a separate cell so that if I typed "California2" into that cell the chart would know to use that named range as its new source data?

View 7 Replies View Related

Expanding Range For Chart Source

Jul 6, 2007

I have a problem setting up an offset range on my charting example ( attached).
My X axis will always be row A

I want my charting range to be more than just the one column and since the months will keep expanding I'm looking fopr the easiest method to do this.

My range could start at 1 row and finish at any other. User Defined.
Bu I want that whole range to be included in the charts. This is a stacked chart.

So in my example I'd liek the shaded bit as a stack chart. 3 bars for 3 months with 4 stacks in each one.

View 9 Replies View Related

Mirror Chart Source Range

Jun 17, 2008

How can I write a macro to mirror x data that will be charted using xy scatter? The original data is entered into excel using measuring equipment with a footswitch. I need excel to automatically mirror the incoming data as it comes in. It should look like my example only with 2 more columns to the right with mirrored x data in the 1st and the same Y data in the second. Example.xls

View 9 Replies View Related

Source Chart Range Until Empty Cell

Jan 16, 2013

I am writing the following code to set the source data for a column chart. the source should be F13 until last cell, which is F862

VB:
ActiveChart.SetSourceData Source:=Sheets("Input TE country").Range("F13:" & ActiveSheet.Range("F13").End(xlDown).Address), PlotBy:=xlColumns

Now it selects F13 until the last cell, which is F65536.

View 5 Replies View Related

Changing Chart Source Data Range

Nov 30, 2006

Each week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.

View 3 Replies View Related

Dynamically Update Chart Source Range

Aug 31, 2007

I'm trying to create a chart and set its source dynamically.

I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,

ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))

And by using the chart wizard.

Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")

Setting the XValues property gives an "Unable to set the XValues property of a series class".

Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.

I've also tried

resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring

When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.

Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.

View 7 Replies View Related

Adjust Chart Source Range Via Combo Box

Mar 31, 2008

I have various charts others view and require the data range to change in 12 month intervals, ie Jan07 -Jan08, Feb07-Feb08. I currently move the ranges manually every month but find the it would be more interactive for others if a drop down combo box on the chart were used enabling the user to choose which intervals to view.

View 5 Replies View Related

Set Range Variable Using Variable Row Number

Mar 11, 2008

I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.

Dim Totals As Range
Set Totals = [U37: AE37]

Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.

View 3 Replies View Related

Range Name + String As Source For Data Validation DropBox

Jul 16, 2009

I have several cells with data validation's drop Box. Every data validation uses a range name as source (myColorList). Here goes the hard part: I need a new value inside the dropBox list, but The problem is that I can NOT modify the "myColorList" range.

-- Is there any way to build up the dropbox source using the range name and the new value? Something like

View 2 Replies View Related

How To Set Table Range To Automatically Adjust To Source Data

Oct 17, 2013

How or if it is even possible to have a table automatically adjust its range or the number or rows it has based on another table

I have two tables:

Table1 is raw data that is added two daily

Table2 has formulas with column header references to Table1 that processes the information and converts it to a readable format. (basically barcodes to words)

Is there a way to get Table2 to automatically resize to the same number of rows that Table1 has whenever data is added??

View 2 Replies View Related

Choosing Data From Defined Work Source Range

Oct 30, 2008

My problem is in this defined range in "C30,D30,E30, F30 & G30" there is a possibility of more than one entry. If that is the case the entry would be in "C31,D31,E31, F31 & G31" If this is the case I need to pick the later which is "E31, F31 & G31" not

"C30,D30,E30, F30 & G30". How can I do that?

View 8 Replies View Related

VBA - Retrieve Chart Series Source Data In Range?

Jan 13, 2010

Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?

This is what I've written so far:

Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer

[Code]....

As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.

View 4 Replies View Related

Data Point Labels On Filtered Source Range

Feb 24, 2007

i'm using this source to add labels to data points in charts:

Sub AttachLabelsToPoints()

'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String

' Disable screen updating while the subroutine is run.
Application. ScreenUpdating = False

'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula

the code is from ms and works. (for some reason they also declare variable 'chartname' although it's never used, anyway). the full thing here: [url]

i actually have my source data filterable. so depending on the filters chosen, the chart updates itself. so it removes data points in the chart as more filters are used.

the problem is that the labels ignore the filters. the code above just goes down the column to grab the labels grabbing values in order, even if they have been filtered out.

a cumbersome workaround would be to copy the filtered data to another range and use that for the labels. this is neither optimal because i have lots of data or elegant.

View 9 Replies View Related

Dynamic Named Range As Chart Source Data

Jun 16, 2008

I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:

Sub Update_Center_Chart()
Sheets("Center Data Chart").Select
ActiveSheet.ChartObjects("Center Data").Activate
ActiveChart.SetSourceData Source = Range("CenterData")
End Sub

I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.

View 5 Replies View Related







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