Selecting Ranges On Different Sheet Using Union
Nov 7, 2013
I am writing a UDF to find the average of two ranges, which start at the StartDate and then go back a certain number of days, that are on another sheet. When I use Union to try and unite my two ranges the resulting range just equals the first range.
VB:
Public Function ModifiedAverage(StartDate As Variant, SheetTenor As String, History As Double)
Dim DateRow, NbCols As Double
DateRow = Application.WorksheetFunction.Match(StartDate, Sheets(SheetTenor).Range("A:A"), 0)
NbCols = Application.WorksheetFunction.CountA(Sheets(SheetTenor).Range("6:6"))
[Code] ....
View 1 Replies
ADVERTISEMENT
Dec 29, 2008
Quick question: When using "With" is it possible to specify 2 ranges, or will I need to do 2 different with blocks? The reason I say not using Union is because I need my ranges seperate. I run 1 set of commands on Range 1, and 1 set of commands on both Range 1 and 2.
View 3 Replies
View Related
Apr 29, 2009
(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:
View 6 Replies
View Related
Mar 3, 2008
I'm trying to union two different ranges that exist on two different sheets and then copy the unique values into a third range with just the unique values. I was going to then use the unique range as my rowsource in a listbox. This is the code I was working with so far but i'm getting a compile error (method range of object failure).
Private Sub UserForm_Initialize()
Dim range4 As Range
Dim range3 As Range
Dim range2 As Range
Dim range1 As Range
Set range1 = Worksheets(1).Range("MyRange")
Set range2 = Worksheets(2).Range("MyRange2")
Application.Union(Range("range1"), Range("range2")).Select
Selection = range3
range3.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=range4, Unique:=True
Me.lstone.RowSource = range4
End Sub
View 9 Replies
View Related
Oct 7, 2008
I have the following two ranges in the same spreadsheet: A1:A1000 and B1:B1000. Both contain data that I would like to manipulate. Let's assume I would like to add "1" to every cell in the two ranges. This is what I have so far:
Sub Test2()
Dim wb As Workbook
Dim ws1, ws2 As Worksheet
Dim rng1a, rng1b, rng_union As Range
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("testsheet")
Set ws2 = wb.Worksheets("result")
ws1.Select
Set rng1 = Range(Cells(1, 1), Cells(1000, 1))
Set rng2 = Range(Cells(1, 2), Cells(1000, 2))
Set rng_union = Union(rng1, rng2)...................
Function ADDONE(rng)
n = rng.Cells.Count
ReDim rng_add(1 To n)
For i = 1 To n
If rng(i) = "" Then GoTo NextIndex
rng_add(i) = rng(i) + 1.............
End Function
I have to somehow combine the two ranges because the data needs to be manipulated simultaneously. Instead of having the range of results returned to me (see worksheetfunction.transpose), I could have calculated the variance, average etc... of the results, too. I'm using the transpose function to show that the operations in function ADDONE are only conducted for the first range of cells (rng1) that I have joined with the union method. I presume that Union might not be the correct method but I don't know ehich other method to use.
View 9 Replies
View Related
Oct 21, 2008
I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do:
Select cell C192
Select Shift and R-Arrow so that C192 and D192 are selected
Select Ctrl, Shift and Up Arrow so that C1:D192 are selected
Select Shift and Down Arrow so that C2:D192 are selected
View 8 Replies
View Related
Aug 13, 2009
I have two named ranges, LV1 and LV2 and wish to select them for printing. If the ranges were fixed I would use - Range("A2:H4,A8:H10").Select - how do I do the equivalent using the named ranges in vba?
View 2 Replies
View Related
Nov 30, 2011
I run a macro on a spreadheet in which the rows increase daily. In the macro I simply recorded an end.down to select a column of data which ends up being fixed row numbers in the code so the next time I run the macro it only selects the range I selected last time.
View 1 Replies
View Related
Jan 30, 2008
I have four ranges of data in a worksheet that are sorted in order by the value in the final columns. Based on this value I would like to flag the highest five values from all ranges by adding a value in the adjacent cell. Is there a formula/function that I can use to identify the highest values from a number of data ranges?
View 9 Replies
View Related
Oct 31, 2009
does anyone have any suggestions for selecting (highlighting) all the named ranges on a sheet?
View 9 Replies
View Related
Dec 23, 2006
I'm trying to get multiple seperate ranges selected but Excel interprets it as criteria even when I use Insert> Function. The problem probably comes from the use of commas in formulae. Example:
I want A1:E1 and A3:E3 and A5:E5 to be the range involved which leads me to construct:
= COUNTIF(A1:E1,A3:E3,A5:E5...
However, Excel interprets the 2nd and 3rd values after the commas to be criteria or something else.
View 3 Replies
View Related
Aug 7, 2009
I have a macro in which I have a string variable named RangeName. I use this variable to select a range of data to copy into an powerpoint slide. However I cant select multiple ranges of data to copy. For eg. when I type:
View 11 Replies
View Related
Jul 8, 2014
I have a list of data that displays data by day, I want to select only the data for the for the current month and then the same day time frame for previous months in the data set.
E.g.
Today's date 08/07
Full days into month 7
Formula to sum data in columns B that only looks at dates 01/07-07/7
I will the adjust that formula for a January date that only pulls data for 01/01-01/07
Then repeat for Feb-June
View 3 Replies
View Related
Jan 10, 2012
I am using Excel 2007 and the following code is generating an error 1004 (Method 'Range' of object '_Worksheet' failed) on the .Range select line. I am trying to select a bunch of noncontiguous ranges and then format them all at one time.
Code:
With Sheet1
.Range("B9:G9,B10:D11,E10:E11,F10:G11,A13:G13,A14:D20,E14:E20,F14:G20,A22:H22,A23:D24,E23:F24,
G23:H24,A26:H26,A27:D28,E27:F28,G27:H28,B30:G30,B31:C32,D31:E32,F31:G32,B34:G34,B35:B36,E35:E36,C35:D36,F35:
G36,B38,B39:C40,D39:D40,E39:F40,B42:G42,B43:D50,E43:E50,F43:G50,A52:G52,A53:C54,D53:D54,E53:G54,
G61:G62,H65:H66,A56:H56,A57:H60,A61:F62,A64:H64,A65:G66").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
I've written longer lines of code, so I don't think it's a line length issue. Is there a limit to the number of ranges or cells that Excel can select at one time?
View 4 Replies
View Related
Mar 14, 2014
I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.
I want to hide the data sheet and the the code doesn't work.
How do I change the import code so that I do not select the sheet?
[Code] ......
I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.
View 1 Replies
View Related
Mar 28, 2012
I have 5 sheets in a workbook. After a code has run I want each sheet to be in A1 when they click on that tab (as there could be 50,000 rows on each), but when I try recording it it doesn't do it properly (Sometimes the A1 cell is active but the view is somewhere down the bottom of the sheet).
View 2 Replies
View Related
Feb 4, 2014
Base on this thread as reference:
[URL]
I have the same issue, but my data are all in 1 cell instead of separate cells.
The result on my sample Sheet 2, I use RIGHT, MID to pull the data, so I wonder if I can run a macro to have all the data in 1 click.
The header in Sheet 2 is to show you the order of the data to pull, having them or not is not the main thing. I need the data in the same order, that is more important.
View 3 Replies
View Related
Feb 11, 2013
I am trying to basically select the last sheet in a workbook, copy the contents, and paste into a new sheet which gets its name from an inputbox. I keep getting a select class error (1004).
Every month i end up adding a sheet and copying everything from the previous month, and then updating a few details which then updates my graphs. Trying to automate the process.
Here is what i have so far.
Code:
Sub Create_Report_Data()
Dim month As String
Dim wcount As Integer
[Code]....
View 2 Replies
View Related
Jul 25, 2008
When I am trying to select a data range on another sheet within the same document, I get an error. My code looks like this:
activeworkbook.sheets("mySheet").Range("A1:D3").Select
View 9 Replies
View Related
Aug 16, 2006
how I could go about selecting all of the "rectangles" on the active sheet. I think I have around 50 on one sheet so it's a real pain in the neck some times.
View 5 Replies
View Related
Jul 15, 2014
I'm trying to make a code so that when optionbutton1 is checked, it allows you to right click to select a cell, then right click on a different cell and select that cell as well as the previous cell, etc. Here's What I have so far.
[Code] .....
View 6 Replies
View Related
Aug 20, 2014
I have a workbook that I want to copy two separate ranges from and then post to two different sheets. Range one on the active sheet from A4:K(no of rows will vary) to sheet Savoury Year, and range two A64:K(no of rows will vary) to Site Services Year. When pasting I want to paste it to the next empty row down.
Copy of workbook attached : Weekly Accounts.xls
View 4 Replies
View Related
May 17, 2014
The last few days I have been trying to figure this out with no luck whatsoever. I am using Excel 2010 32-bit with power query on a Windows 7 64-bit computer. I was going the use the html creator, but the dl link was down and I had to use screenshots.
I have a sheet titled "LeadSheet" that contains multiple data blocks of information (around 20). I have attached 2 examples of these data blocks and 2 examples of results needed below. I tried to create them so they would fill in the gaps of my explanation.
All of these data blocks are 7 columns wide and vary in row size from 10-250. The blocks of data all have titles on the 5th row, but are not headers, and then data beginning directly underneath. I hesitate calling them ranges because I only selected one data block as a range and named it "lead1" and then stopped not knowing if I was headed the right direction.
I first tried to create a table out of the range, but was unable to do so because the "Name" column is an array. The phone and address columns are populated by VLOOKUP. And although the images show the column names as the same for name, phone and address theyare actually different, like name-firm1, name-firm17,address-firmxyz, etc, etc. I not putting that in the images. The "Option 1", "Option 2", "Option 3" and "Option 4" columns are generated using a =IF formula.
I have another sheet titled "ResultsSheet".I'm needing a way or code to copy entire rows from the multiple data blocks/ranges in the "LeadSheet", and paste it in the"ResultsSheet", based on any value occurring in any of the"Option" columns within the individual data blocks/ranges along with appending the "Option #" title to either end of the copied row.
The "LeadSheet" is constantly being updated so information is being added and taken off all the time. That being said, is there anyway to make it update as soon as the "LeadSheet" does or on a timed interval so that the"ResultsSheet" is always up to date. And also prevent it from continuing to re-copy over duplicates of information that has not changed?
Ifthe "Option #" result is the exact same percentage then the order does not matter between them.
Ifwithin the same data block/range both "Option" columns have data it is usually because of some error and is most likely bad datathat does not need to be copied. i.e. "Steven Seagal". If that makes this task much more difficult I can live with it.
If within two or more different data blocks/ranges the same name and info appears that if fine because the "Option #" will always be different. i.e. "Chuck Norris".
I am also trying to make the "ResultSheet" ordered from largest percentage to lowest, but I assume that should be done once the data is on the "ResultsSheet". No headers or titles are necessary on the "ResultsSheet"
Data Block example 1:
Data Block example 2:
View 5 Replies
View Related
Nov 9, 2009
I have 2 comboboxes in the complett worksheets. By selecting some values in this comboboxes, I need some code that will copy automatically some data from worksheets a and b, to worksheet export.
For example if I select "a" and "A26" in the comboboxes, I need range E26:B26( 4 col to the right of A26) from "a" worksheet, to be copied with Paste Special /Values/ Transpose into "export" worksheet in range A1:A50. Also I need in range A51 from worksheet "export" the value "A26" selected in the second combobox. Also in range A52 from worksheet "export" the value from range H40 from "complett" worksheet.
View 8 Replies
View Related
Apr 27, 2014
I am trying to avoid selecting sheets in my current project. I have a querytable in A1. I can get it to work if i selects sheet and range.
Can it be done using with as below
Code:
With Worksheets("meeting to look at")
End With
View 1 Replies
View Related
May 25, 2007
Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("SUMMARY")
Set ws2 = Sheets("WIP")
Application.ScreenUpdating = False
With ws2
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select ' Clear
End With
x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
The code is run from ws1. I put in the .select for now because I don't want to loose my info on ws1.
I trying to clear the range A2:C2 all the way down to where info is before doing the Do Loop which will repopulate the same range from ws1.
If the rest of the Do Loop code is needed, let me know.
View 9 Replies
View Related
Aug 15, 2014
I'm writing a function which includes the need to merge 2 ranges into 1 new Range object. The problem is that adding a breakpoint just after this occurs, and viewing the immediates, the new range only actually contains the first input range
For testing, and displaying here I've placed the 2 sub-Ranges into temp objects
[Code]....
The breakpoint is on the next line (not shown), so I can view all the Locals
tempyR1 and tempyR2 both are Range objects containing the correct Range data as expected from the code
However, objExcelRange only contains exactly the same as tempyR2
I'm using the Locals to check as this new combined Range will undergo further processing before being placed onto a sheet, and the combined Range will actually be generated dynamically depending on what options the user picks from a second sheet within the WorkBook - which could in fact be created from between 2 and 5 sub-Ranges
View 7 Replies
View Related
Nov 12, 2012
I'm trying to select the same cell and a blank cell multiple with a UNION(). Is this possible?
Here's a summary of my code:
Code:
Dim c
Dim Rng As Range
Dim blankC As Range
Set blankC = Range("XFD1048576")
Code] ......
Ultimately, I'm trying to copy various cells and reorganize them including blank cells to conveniently use UNION for a quick copy and paste as opposed to explicitly defining where to paste everything.
View 9 Replies
View Related
Aug 24, 2013
I would like to check if selected cells is a union of several ranges or one solid range. I am sure there is a way, but did not manage to find it. How to do it?
View 2 Replies
View Related
Apr 6, 2014
how one can union a selection without looping through the cells? I need to write a noncontinuous range into an array, and it can't be sorted as I'm using specialcells(xlCellTypeVisible).
View 1 Replies
View Related
