I'm trying to create an array of Sheets objects and use them. This gives me a type mismatch on the marked line below. I could just save the sheet names as strings in the array.
Dim SheetList() As Sheets, A As String
Set ORng = Sheets("Options").Range("ReachPagesHeader").Offset(1, 0)
PageCount = Range(ORng, ORng.Offset(1000, 0).End(xlUp)).Count
ReDim SheetList(1 To PageCount)
For X = 1 To PageCount
A = ORng.Offset(X - 1, 0).Text
Set SheetList(X) = Sheets(A) '
how do i create a sheetsarray to include all sheets between First and Last? I plan to loop through each sheet in this array to copy data to a summary page, as per [url]
Do array formulas work if the array is across sheets instead of across columns or rows? I'm getting a #Ref! error when I try to use an "across sheets" array.
I want to populate an array list based on sheet names, but cannot figure out the last line for syntax:
Dim ws As Worksheet, arr() As String ReDim arr(0 To Sheets.Count-1) For Each ws In Worksheets If InStr(1, ws.Name, "Crp-") Or InStr(1, ws.Name, "Reg-") Or InStr(1, ws.Name, "Grp-") Then arr(counter) = ws.Index counter = counter + 1 End If Next ws Sheets(arr()).printout
As I debug it the array is filled with the proper sheets, but I for some reason cannot figure out the syntax for this line? Does it have anything to do with me declaring the array as a string and using integers as the index?
I wonder whether I'm using the code below to copy data from two 'Source' sheets to one 'Destination'.
[Code] .......
The code does copy and paste the correct inofrmation, but the problem I have is that the data from the latter sheet i.e. "IDEAS Actuals" overwrites the data from the "IDEAS Forecast" sheet.
How I would be able to change this so the data is copied underneath each other.
Sub Macro2() Dim stgSheets As String, Cnt As Long Dim arrSheets() Cnt = Sheets.Count ReDim arrSheets(Cnt - 1) For i = 1 To Cnt stgSheets = stgSheets & Sheets(i).Name arrSheets(i - 1) = stgSheets 'sSheets = sSheets & "", "" Next i
Sub TabColor() Dim mySheets As Worksheets Dim mySheet As Worksheet Set mySheets = Worksheets(Array("sheet1", "sheet2")) For Each mySheet In mySheets With mySheet.Tab .Color = 9 End With Next End sub
I'm getting a type mismatch at line 4. Can I not group the sheets like this? This is just a test program, my real one would have 10 or more tabs in the array.
I have a workbook with several sheets, some are permanent and then there are certain sheets whose quantity varies depending on how many sub-contractors we use on a given job. The workbook starts as a template and there are 10 sub-contractor sheets to start with, if we only have 5 subs then the last five sub sheets are deleted. The sheets start out with the tabs named "Sub 1" "Sub 2" etc. but after the book is set up the sheet tab names are changed to the sub name. I've included some partial code below, with the goal being to run code on the sub-contractor sheets no matter the tab name or how many there are.
So the workbook has 5 "non-sub" related sheets, I was thinking that if I could do a sheet count -5, and get that number into the array it would accomplish what I want to do. And if someone added a new sheet it would still be processed with the others.
Public Sub SubConEstNum() On Error Resume Next Dim S As Worksheet, names As Variant, i As Integer names = Array(6, 7, 8, 9, 10, 11, 12, 13, 14, 15) For i = LBound(names) To UBound(names) Set S = Sheets(names(i)) S.Range("T3").Value = S.Range("S3").Value S.Range("T1").Value = S.Range("S1").Value S.Range("T2").Value = Sheets("Bill").Range("K1").Value
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 have created a macro that loops through and creates an array of the visible sheets. Now I would like for it to print each of those sheets out. I think my main mistake is in my declaration of the array type, since I have not worked with arrays much before.
how to make the following code operable. Currently when I run it I'm getting a "Run-time error (9): Subscript out of range" error.
I need a code to select sheets with pages less than 15 to print out. Also, a code to print out just the first 5 and last 5 pages if its more than 15 pages.
I've tried this so far but no success:
For X = 1 To Worksheets.Count
If Y Is Nothing Then Set Y = Sheets(X) Else If ExecuteExcel4Macro("Get.Document(50)") < 10 Then Set Y = Union(Y, Sheets(X))
I am currently using this code to place names in a combobox (active X). How can I use an array to perform each line to all 12 months without repeating the code over and over?Here is what I have:
I am trying to graph data from an Array of sheets. I keep getting many errors... I tried using Cases too but this failed, it graphed the first Sheet but nothing else.....
Before Array Works! (Uses Cases)
Code: Sub GraphTest4() Dim LastRowGraph1 As Long Dim ws As Worksheet
Issue with array that's bolded The way it is set up is to rename the 3rd sheet to MyFileName and rename the last sheet to MyFileNameTwo. The file names will remain constant. They will always be the 3rd and last sheets, but the number in between will vary. Is there anyway to select the 3rd sheet through the last sheet to delete these? When I use the array it wants sheet names but those are based on multiple variables in other workbooks.
I have a list of cell entries that I want to find and replace with different text or a number. My code below is 4 of them. I am trying to do the replace over 3 different sheets at the same time but I am only changing the 1st sheet with my efforts.
Working with an existing macro from this forum. How do I create the array showing the number of sheets found? I know from a MsgBox that two sheets are found.
I am trying to use the SUM IF Array formula to sum a group of numbers that fall under a heading of reference numbers over several sheets of data. For example purposes lets say my spreadsheet looks something like the below.
A B C D E 1 2600000248391 2600000393805
[code]......
The first two digits of the heading numbers are the criteria I am trying to use to separate and sum the data. For example I need to sum the value of the data below headings that falls between 1400000000000 and 1499999999999.
For the example above I used the below formula for the current Sheet and it works fine.
I have the below VBA code associated with a sheet called "Select View". When the workbook opens there is another code that displays only "Select View", all the other sheets are hidden. In "Select View" there is a data validation list. Based on the selection the user gets to see only certain sheets. For example, if the user selects "Region 1" they see only the sheets "Region 1", "Panama City" and "Pensacola".
As you can see some of the names in the "Target.Value" are the same as sheets in an array. The ones that do are lead schedules, that is information from other sheets in an array roll into it. That is values from "Pensacola" and "Panama City" flow into "Region 1", the lead sheet.
What I am trying to do is finish the code where based on the "Target.Value" selected it automatically takes the user to the lead sheet. For example, when the user selects "Region 1" in the data validation it takes the user directly to the sheet "Region 1". The user of course can also see the other sheets in the array, that is "Pensacola" and "Panama City" and can manually select the tab to view or go into one of the later
Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("B10"), Target) Is Nothing Then For Each sh In Sheets(Array("Panama City", "Pensacola", "Region 1")) sh.Visible = Target.Value = "Region 1" Next sh
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
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.
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
