I am trying to do something that sounds fairly straightforward but have not been able to achieve the final result.
I have a sheet which has multiple range addresses stored in different cells and so far I have been able to pass the Range address to Range variables by looping through a range array. Now I would like to select these multiple ranges. My code looks as below;
VB:
Dim Cnt As Integer
Cnt = Sheets("Sheet1").(Range("1040").Value ' Cnt holds the the total number of ranges to be specified in Sheet1
Dim RStr(1 To 80) As String ' This will hold the range addresses specified in Sheet1 in cells C1041 to C1120. Upto 80 range addresses could be specified.
[SIZE=2] Dim Rng(1 To 80) As Range ' This will store the ranges based on the range addresses stored in RStr()
Dim i, j As Integer,
[Code]...
I am not sure on how to select multiple ranges using range array hence tried Union() however it is obviously not correct and throws an error.
I have 2 ranges which on their own seem to work fine (I have check by just putting ".Select" at the end of both of them). The issue is that I want to apply the same border format to both ranges, however, when I try to put them together in a "With" statement it includes all of the range from the top to the bottom and not just the 2 two ranges. Ranges being used:
The User has made multiple selections with the mouse. The spreadsheet is filtered. The user will usually make different multiple selections on the following columns: A and X through to AR (inclusive).
I just need some code to capture these various multiple selected ranges so that I can copy the selected range as shown below:
I'm trying to create a single array from multiple ranges... I'm not sure what syntax to use:
Code: Dim dat4() As Variant Set r = Sheet13.Range("rsqlassetid") Set r2 = Sheet13.Range("rsqlparentcat") dat4() = (r , r2)
I can create an array with multiple columns from a range if the columns are next to each other but in this instance they're not.
These 2 ranges both have the same number of rows and I'm trying to combine them into a 2 column array, but not sure how to make it work without looping, rediming the array and using a secondary array to preserve the data...
I'm trying to open multiple files, pass them into an array and then work on them but the code below doesn't work (receive a Run-time error '9' message)
Public aFile As Variant
Sub MultipleFiles ()
MsgBox ("Please select multiple book files (holding CTRL as you click on flies) before you click open")
If MonthView control can be set to allow users to select multiple ranges and enter different dates into those ranges? I know I was able to do with with the previous Datepicker control and I thought it was as easy as setting the Show Modal property to true.
Is it possible to form a single Range object from ranges on multiple sheets. So for example, would it be possible to set a Range object equal to cells A1:D146 from Sheet 1 and A1:B49 from Sheet 2 and if so, what would be the syntax? For the first I have: Sheets("SelectData").Range("A1:D146").SpecialCells(xlCellTypeVisible) but I wouldn't know how to proceed from there.
I need a 'simple' vba that outputs all the dates in a range that correspond to a particular ID in a column as well as that same ID in a column beside the dates.
So basically using the data in columns A to B as input, to get columns E and F as output.
cubspline(Xval As Double, XRange As range, YRange As Range) As Double
The problem is that XRange (and also YRange) is in different areas of the spreadsheet. I want to combine these areas into one range which I can pass on to the cubspline function. What is the easiest way of doing it? I'm looking for something like a "union" function for ranges in Excel.
I'd like to select the start date & end date for multiple Pivots (on one sheet). Expl. if I put in a date in the "Start Date" cell : 01/01/2010 and another date in the "End Date" cell : 15/01/2010. Then all the pivots on the sheet should only show the date between 01/01 and 15/01/2010.
The dates in the pivots are under column "Date". I have a code but it only selects 1 pivot and it doesn't refresh the pivots when you change the dates selection.
I am trying to add a number to invoices (invoice numbers) on a UserForm that is used to make and/or modify scheduled service records for a cleaning business. We fill out the schedule well in advance of when the jobs are scheduled to be done, some customers are on a set schedule such as once a week, everyother week or once a month. Some just call in when they need cleaning. Each day has 27 rows set aside for possible customers (most are not used). We make out as many invoices in advance as is possible and fill-in the rest as they call-in. This leaves a lot of gaps between days
I have an auto-advancing formula tha takes a helper column to give me invouce numbers when there is a customer listed in column B and blanks space where there is no customer listed. This does make for volitile invoice numbers.
Formula in row 3 of the invoice coulmn "A": =IF(B3="","",H3) Formula in row 3 of helper column "H": =IF(B3="",H2,H2+1)
The helper column only advances if there is a customer listed in column B of that row otherwise it repeates the last number. The invoice column only displays this helper number if there is a customer listed in column B of that row. I can't figure out how to get this to work on a UseerForm with vba.
how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?
I am currently using the array formula to find out the most recent date a business as referred a deal. I have been using the below array formula to figure this out...
However, we have now have two types of deals. Opps and Suspects. SO now i need to figure out the most recent date from Opps!G:G and Suspects!G:G... regardless of what the deal type is. Hope that makes sense.
Basically i need to have a formula along the same lines as above but MAX date from if conditions from Opps OR conditions from Suspects
I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?
Private Sub CommandButton1_Click() Worksheets("Sheet1").Activate Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate ActiveCell.PasteSpecial End Sub it errors to: SELECT METHOD OR RANGE CLASS FAILED
(1). selecting a specific range to copy that is within a table but starting at a specific row to the bottom of the table. E.g. Range("C4").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy
(2) finding the last row and then setting as a variable: Range("D" & Rows.Count).End(xlUp).Select lLastRow = ActiveCell.Row
I have a protected worksheet with 60 columns (20 sets of 3), and want to give my users the ability to hide the columns they're not using. There's a row of unprotected cells below the columns in question with data validation allowing a choice of either Show or Hide, and want my macro to act based on thier choices.
Since it's a large spreadsheet, I don't want to run 60 (or 20) different hide commands, if at all possible.
Is there a way to read the 60 different Show/Hide cells (or 20 range names) and join those into a single VB range so I can run the EntireColumn.hidden = True statement once?
I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...
i'm having a really hard time figuring out how to use target. offset to grab a range of cells. For Example:
I am currently running a macro that searches column "D:D" for data. When the data is found, i want to be able to Grab columns D to AG for that row, and apply conditional formatting. It seems like using Target.Offset should be able to accomplish this, but i can't figure out how to specify a range using Target.Offset. Currently, i have to specify each cell i'd like to select, and apply formatting one at a time, which has bloated my code considerably (and causes me to run out of room very quickly).
I am trying to copy and paste between workbooks that are defined in an array. However, I get an out of range error when the code first reaches the line Workbooks(Wkbk(WkbkNum)).Activate.
Sub Gather_Risks()
Dim MasterRow As Integer ' Declares row number in Master Worksheet Dim RowNum As Integer ' Declares row number in active array worksheet Dim Wkbk(13) As String
I would like to set up VBA codes to generate a variable number of transition matrices. I would like to know how I can define dynamic ranges instead of coding each and every range. In the codes below, I have to define each range one by one instead of using a loop.
Sub TransitionMatrix() Dim P1 As Range Set P1 = Cells(2, 2).Resize(3, 3) Dim P2 As Range Set P2 = P1.Offset(5, 0).Resize(3, 3) P2.Cells(0, 1) = "P2" For i = 1 To 3 For j = 1 To 3 P2.FormulaArray = "=MMULT(" & P1.Address & "," & P1.Address & ")" P2.BorderAround Weight:=xlMedium Next j Next i Dim P3 As Range Set P3 = P2.Offset(5, 0).Resize(3, 3)........................
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Code: Sub populate() Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
