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] .....
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 RelatedQuick 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(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 RelatedI'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
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] ....
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
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.
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'How do I use a FOR NEXT loop on a union to just list all cell values in Col A?
'Just need a little help getting a handle on ranges and the union of ranges.
' This is my first time posting a question so any patience is appreciated.
Dim r1 As Range
Dim r2 As Range
Dim myMultipleRange As Range
Dim xCell As Range
Set r1 = Sheets("Sheet1").Range("A1", Range("A65536").End(xlUp))
Set r2 = Sheets("Sheet1").Range("B1", Range("B65536").End(xlUp))
Set myMultipleRange = Union(r1, r2)
r1.Select
For Each xCell In r1
'Ive been using this type of code to access anything I need from a sheet.
Debug.Print r1(xCell.Row, 1), r1(xCell.Row, 2), r1(xCell.Row, 3)
Next xCell...............
columnA contains account numbers sorted in ascending order i need to use the union method to select all rows where the account number changes so that later i can insert rows between different account numbers in one action. i tried to use the following code but an error occurs stating 424 object required ....
View 7 Replies View RelatedI have two worksheets sheet1 & sheet2 in Workbook3 both get data from two different workbook1 & workbook2 using Microsoft Queries. Column Headers are same but contentes and no. of rows are different in these sheets. I would like to combine data from these sheets into sheet3 of Workbook3 so that i can create pivot table on that. I tried using Union Method, But it gave me "Run time error '1004'. Method 'Union' of object '_Application' failed in the following line
Set myMultipleRange = Application.Union(r1, r2)
Union method works fine when combining ranges from the same worksheet.
Multiple consolidation in Pivot table is not desirable to me as i have to group data using two columns and i could not get what i wanted using two page fields.
I have created named ranges for each sheet data. Is there an alternative to Union method for combining these named ranges from two worksheets into third sheet through VB code?
I am trying to create a union range that is comprised of two cells that are no where near each other on a worksheet. I keep getting the object required error. Everything else works perfectly.
Dim myRng As Range
Set myRng = Application.Union((Range("IndData1")), (Range("IndData2"))).Activate
Do Until ActiveCell = ""
Selection.Copy
[Code] .....
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.
I have 4 sheets. In each sheet, I have a column of data for every hour of every day of one year.
In the 1st worksheet, I want to make a chart using :
- for Y values : the average of the data for each day
- for X values: the day
In the 3 other worksheets,
- for Y values : the data for each hour
- for X values: the hour
To make it more simple, I will first build in the "D" column the data of the average data for each day (so, every 24 columns)
Here is my
Dim lNbSheet As Long ' sheet number
Dim rSheetData As Range ' what will be my Y values
Dim rSheetTime As Range ' what will be my X values
Dim sSheetName As String ' current sheet
For lNbSheet = 1 To 4
'selection of the Y values
Sheets(lNbSheet).Select
sSheetName = ActiveSheet.Name
I get the error "methode SeriesCollectiosn of Object _chart failed;
Also, if you have an idea that could make me avoid writing all the new average-for-the-day data in column D (using it directly),
I have a code in software that exports results to excel. But it stores the numbers associated with indices as text. I need to change all of them to numbers. So I have written the following code, that search through the folder, converts text to numbers and colses the file.
Sub ConvertText2NumberFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C: ...analysis"
[Code]....
The problem is it takes time becuase it is going through A LOT OF cells. I only need at most the first 4 columns and the first 2 rows. So I wanted to use the union. I wrote the following:
Set IndexColumns = bk.ActiveSheet.Range(Columns("A") & ":" & Columns("A").SpecialCells(xlLastCell))
Set IndexRows = bk.ActiveSheet.Range(Rows("1") & ":" & Rows("1").SpecialCells(xlLastCell))
Application.Union(Range("IndexColumns"), Range("IndexRows")).Select
Which doesn't work! Morover it is only for one column and one row.
NOTE1 : Like any matrix, the intersection of index columns and index rows is blank!
NOTE2 : The lenght of indices is different from one file to another
I created a union query in Access to join two tables (Projections and Actual Sales). The query produces the results I want. I need to create a pivot table in Excel using the union query as the source. When I pull up the data import function in Excel, the union query does not appear. Do I need to do something else? I have tried to create a select query where I select all from the union query and I can find that fine.
When I use this query to create the pivot table the results end up all zeros when I try to sum the values. It creates some crazy results when I show it as count of also.
I can provide the data in either the Access database or Excel spreadsheet.
I have to find the union of 2 columns in excel and club those 2 columns into a single column with values in the increasing order.eg: column 1- 0 2 4.. , column 2- 1,3,5.. final result in column 3 should be 0 1 2 3 4 5...plz let me know the code for this that i can run in VB editor(- excel 2007)
View 9 Replies View RelatedIf a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View RelatedI tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:
1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.
I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................
I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-
If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.
I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.
Is there any way to do that as I tried
VB : Listbox.enabled = False
and
VB : Listbox.locket = True
in Listbox - Click/Change but no desirable results got.
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
I have a lot of text in a cell and I want to create a Hyperlink using only 2 or 3 words and not the whole text. Unfortunately, it seems as though the hyperlink can only be from the cell itself. Is it possible with Excel to create a Hyperlink from a section of text in a cell, and NOT from the cell itself?
View 4 Replies View RelatedI would like to run a macro by clicking a cell. I don't want a toolbar button/menu item and Hyperlinks don't work. Can this be done?
More info: My macro selects the current row in Sheet A and moves it (cut & Paste) to Sheet B in the workbook. The macro then goes back to Sheet A and deletes the now blank row.
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z$29:Z$39)-1)
Above is the formula that I have, I am putting it into Column L. When I do auto fill every row that has data in J and K the formula goes into as you would expect.
However the formula does not activate until I double click inside each individual row and hit enter. Therefore if the calculation in L4 was 1.0, then 1.0 will appear as the calculation in every row of Column L until I double click in each row individually and press enter.
Is there a way to disallow certain cells to be clicked on?
View 9 Replies View RelatedWhen I click on the userform that opens when the workbook does I get this error:
Run-time error '13':
Type Mismatch
This is the code highlighted
DT=UserForm1.TextBox3.Text
i got the userform to popup when the workbook is open and the buttons work fine... only problem is when i click on a button, it will ask the user yes or no... if the user press yes, it performs the required macro without any problem... but when the user press no, not only it will close the question, but also closes the userform... I would like the 'no' to close the question but not the userform...
View 4 Replies View RelatedI have a chart that I just added some dynamic text boxes to. That is the text is linked to a cell so that the text is automatically updated.
I also have a scroll bar that is used to adjust one of the cells. However, now once I click on the scroll bar it just disappears. I can click on the space where it is located and it will appear for a split second. Also, if I scroll down in the worksheet and then back up over the scroll bar the image will be refreshed and I can see the scroll bar again.
I didn’t have this problem until I added the dynamic text boxes, it worked fine with static text boxes.
Does anyone have any idea how to get around this, or do I have to stick with the static text boxes?
