Using For / Next To Store 3D Array
Jul 30, 2012
I am trying to write a VBA procedure that uses nested For/Next loops to store the contents of a range in three worksheets to a 3 D array and then input the array into another location.
I have no problem doing this for a 2 D array, but am completely stumped on what changes I need to make in order to perform the same task on a 3 D array.
I will post my code for 2 D arrays below and will also post the uncompleted 3 D code, although, it is currently of no use because I am so lost on the 3 D array.
How do I reference sheet1,sheet 2 etc. when declaring my 3 D array? I know how to reference the rows and columns for my 2 D array by using the cells or range object, but what to do for the different sheets.
Two D Array:
Code:
Public Sub For_Next_Two_D_Array() Dim I As Integer
Dim J As Integer
Dim MyArray(4, 4) As Integer
For I = 1 To 5
For J = 1 To 5
MyArray(I - 1, J - 1) = Cells(I, J).Value
[Code] ........
Three D array:
Code:
Public Sub Store_ThreeD_Array()
Dim I As Integer
Dim J As Integer
Dim S As Integer
[Code] ......
View 6 Replies
ADVERTISEMENT
Jun 19, 2008
I would like to use array v to store all the results of the for loop u...How can Ido it?
Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
AnsRange1 = myRange.Row
Dim u As Integer
Dim v As Integer
Dim var() As Single
v = 0
For u = 23 To 24022 Step 9
var(v) = u
Next u
If Not (AnsRange1 = v) Then
MsgBox AnsRange1
Else
Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
End If
End If
View 9 Replies
View Related
Dec 3, 2008
i am not good in programming.In an outlook i am trying to write a maro.
I am reading lines from a text using readLine(), how i can store each line into an array using vbscript. I write the code as follows,
While Not F.AtEndOfStream
s = F.readline
Start = InStr(s, "@")
If (Start > 0) Then
- Here i need an array, when start>0 , store that line into an array
View 9 Replies
View Related
Apr 17, 2008
I can't quite seem to figure out the syntax for pulling a Range variable out of an array of type variant. I always seem to receive the error message "Object variable or With block not set", an example of my code as follows:
Dim currentRange As Range
For i = 2 To UBound(myArray)
currentRange = myArray(i)
' insert code here
Next
I have also tried:
Dim currentRange As Range
For i = 2 To UBound(myArray)
Set currentRange = myArray(i)
' insert code here
Next
Which results in the error "Object required". What is the correct syntax?
View 4 Replies
View Related
Mar 8, 2013
I have in column B, cells that have any number, one number or several like B2.
#
A
B
C
D
E
[Code]...
Thinking that I have a loop for rows 1 to 3:
When in column "A" is "No", I want to consider values in C and D to get:
Code:
a[1]=Cells(1,"C") & "-" & Cells(1,"D") & "-" & "ABC"
But when in column "A" is "Yes", I want to store in array each value within cell in B (in this example B2) to apply later a For/For Each
over each number (in this example are 3 values only within cell B2), something like:
Code:
a[1]=Value_In_B2[1] & "-" & "ABC"
a[2]=Value_In_B2[2] & "-" & "ABC"
a[3]=Value_In_B2[3] & "-" & "ABC"
View 6 Replies
View Related
Jan 21, 2009
In an excel i have 3 columns they it contains around 12000 records
Group FA Title
A S1 bbbb
A M1 xxxx
A M2 eeeee
A S1 ffffff
A S1 pppp
A M3 aaaaa
A M2 ooooo
A M2 qqqq
A M1 ttttt
Here i need to get the unique FA, so i filter the column FA, my question is, After filter with FA column ,is there any way to store these unique FA(ie S1,M1,M2,M3) into an array using vba?
View 9 Replies
View Related
Apr 20, 2008
I have a list of names from cell A1:A10 in sheet "Input." Each of these names has its own corresponding sheet in the workbook. I want to be able to run the same exact VBA code for each sheet. In other words, I am trying to get my name variable to automatically change to the next value on sheet "Input." I'm sure this is pretty simple to do, but I can't seem to find anything that works!
View 5 Replies
View Related
Nov 18, 2008
I want to perform some operations (basically a secondary filter) based on the values which are currently filtered within a single filter column.
.Autofilter.Filters(n).Criteria1
and
.Criteria2
are great, but what if there are more than 2???
i.e. I have a column containing values L01 to L20.
My column is filtered on L05, L06 and L07 (or some other combination).
I want to extract the values and L05, L06 and L07 and do what I will with them.
View 9 Replies
View Related
Aug 30, 2006
how can i store the values of an autofilter's list in a array using VBA.
View 4 Replies
View Related
May 5, 2012
I am trying to loop through column A and I want to store in an array where I find "App" within the cell value. I am trying to find "App" but will store the whole cell value in the array. I could not figure out the Find method, so I tried the MID function but am having no luck.
Here is my code:
Code:
Sub Arraytest()
Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long
f = 0
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To 1, 1 To lastrow)
[Code] .....
View 1 Replies
View Related
Nov 6, 2009
Look at the tab "testing-allproduct" cell C2. If that value exists in the tab name "store-allproduct", cell c2, then take the value from "store-allproduct" cell a2, and put that value into "testing-allproduct cell" A2.
View 2 Replies
View Related
Sep 16, 2009
I have some code (listed below) that will open 2 groups of files(for testing purposes, I have been using for only 2 file prefixes, but will need for upwards of 10, and more may be added in the future), depending on what files have been opened in the past (it will skip those) and then import the new ones.
I am now trying to clean up my code, and having alot of it abled to be maintained be editing a spreadsheet (administator controlled)
What I would like to do is something similar to:
defvar= cells(1,1) 'where cells(1,1) has all of the info for that file to import
Selection.TextToColumns defvar
I realize this will probably be a little more complicated than this, and may even be its own sub or funtion.
Here is my starting code, and it works fine: ...
View 8 Replies
View Related
Mar 15, 2014
I want to store number as text like green sign display at the top left corner.
View 7 Replies
View Related
Jun 9, 2014
I want to store a list of strings in an array with their position so that I can sort them alphabetically and then be able to put them back in their original order again e.g.
John 1
Charlie 2
Paul 3
Andy 4
etc,
So when they are alphabetized (is that a word?) they become
Andy 4
Charlie 2
John 1
Paul 3
My plan was to store them in an array, but I can't think how to do it. In python, I would just make a list of tuples, what is the best way to do this in VBA?
View 7 Replies
View Related
Jul 29, 2014
Currently We enclosed a Workbook (with exiting VBA code) In this workbook 3 sheet exit (Edit, Details, Summary)
In Details Sheet store Detail Entry of Invoice & in Summary Sheet we store all generated Invoice details in summary form
We want new VBA or correction in existing VBA.
My Exiting VBA work following..
When Enter Invoice Number in Edit Sheet (Cell B2) then Display Entered Invoice Data
First Code Find Invoice Number in Summery Sheet if Invoice Number found then Record Display of Selected Invoice
Here we Edit As per our requirement then We Press Y in Edit Sheet Cell C30
If Found C30 "Y" & "Data ok" in E30 then DELETE Selected Invoice Data From SUMMERY & DETAILS SHEETS & COPY THIS EDITED DATA (RANGE A33 TO G33 In Summery Sheet & Range A35 to A60 in Details Sheet)
My Exiting code work perfectly as per above
We want only following thing
Currently when we copy edited data that move to end of sheet We want to Change & same place where data exit previously before edited. Because serial number disturb.
NOTE : in Editing Time is it possible Few Item Delete or Few Item Add
View 12 Replies
View Related
May 17, 2007
Can I store a value with out storing it in a field? -with the macro ending?
Ex macro1 runs and gets A1.value
Stores the value (Not in a cell)
I later run macro2
Gets the stored value
View 9 Replies
View Related
May 10, 2008
In a cell I will be having many strings separated by a ";" for ex
columna A
a; b; c; d ; e
f; g; h; i; j; k
how to store this in a macro?
View 9 Replies
View Related
Dec 31, 2008
I have a two rows of data one containing names and the other containing corresponding numbers. The names are static and the numbers change on a daily basis. I want to be able to copy the numbers to a static table next to each name on a daily basis (so I can see what the value was a few weeks ago).
Is there anything I can write to do this job?
My thinking was to set a vlookup to grab the data but i'm not sure how this would work because the vlookup would change daily when the numbers change
View 9 Replies
View Related
Aug 11, 2007
I have tried to look for this in several websites and have not found the appropiate answers. So i figured one of the Wizards in this board can help me. Right now when I built a macro it has to be dependent to a file. However I like to build a macro button that when I use it, it will actually just work for any given currently open sheet.
View 7 Replies
View Related
Aug 18, 2007
I'm sure this is a very easy question, but I have been having trouble finding the answer on this and other forums.
Our macro has a search routine where it looks through a column for particular cell contents. Once those contents are found, we want it to save that cell's location, go do something else, and then return to that location afterwards. How would we go about doing this?
View 5 Replies
View Related
May 6, 2014
I have a simple Excel file with some columns as you can see from here:
[URL]
I have a simple formula (COUNTIFS) to count occurrence of certain condition that I specified. everything works fine here, but I also need to write current value of count cell at the end of each qualified row.
Data entry is random and I may work on row 1 and then row 25, so incremental row numbers that is shown by excel is not my answer. I need exact number of occurrence for each qualified row.
excel.png
View 4 Replies
View Related
Mar 19, 2007
I'm looking for is ComplyXL and that costs $300 to buy. So, as a poor grad student, I'm looking for an alternative solution - either a freeware program or a hassle-free VBA code.
So here's what I need the code or program to do: When I ask excel to open a pre-existing file, I'd like it to automatically rename (& compress?) the last used version of the file & save that to a filename-specific archive somewhere (hidden?), before opening the file as requested. Alternatively, I suppose that a mirror copy of each 'just used' file could be created & saved to an archive folder whenever the used file is saved & closed.
--Note: to avoid making copies of EVERY workbook I ever use, I suppose there should also be a button in the toolbar or a file-associated setting that lets me identify which files I want this task to apply to...
Basically I'm looking to have excel automatically save versions of certain key files (like Word's 'Versioning' feature) without having to remember to 'Save As', without the versions taking up much space, and without having to store the older file versions in the same place as the most recent one.
And yes, I know that a similar problem has already been posted & solved here, but they didn't include a way to choose which files used versioning nor (if it's possible) a way to compress the archived versions to save space (maybe by adding it into a WinRAR archive somewhere?).
View 11 Replies
View Related
Aug 2, 2014
I have several words in sheet2.one word per line. every line in sheet1 should be checked and deleted if the line (colum 3 and 4) contains any of the words in sheet2. i decided to go with two for-loops, my Problem is a error in line 7 and 9 (indicated by arrows). it seems to me that ...Cells(...).Value is not allowed for strings. i already tried .Text, checked several VBA Forums but could not find a solution.
View 13 Replies
View Related
Jun 12, 2009
I want to create a macro that will look at the IE windows that are currently open on my machine, check if the window title contains a certain word e.g. “Results” if so return the full title of the window and store in the worksheet.
I have been searching on the internet for quite some time for this with no real results which leads me to believe it might not be possible.
If it is possible I'm not asking for someone to write all the code for me (I realise how annoying that is!!) but if someone could point me in the right direction or even just tell the code that would return the title that would be great.
View 4 Replies
View Related
Aug 20, 2009
I need a bit of help with the below macro which I am trying to create. I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro
View 4 Replies
View Related
Nov 1, 2005
Is there a Function or Formula that can store data from say cell $J$21 and store the result in L21 in the same worksheet and every time the data in $J$21 changes the result moves down 1 ie: L22 etc.
This would happen at the most 20 times a day.
View 14 Replies
View Related
Nov 29, 2011
Is it possible to store format of a sheet in a variable?
I have one sheet. i want to select all cell. then copy the formatting of this sheet(template) in a variable. is that possible? does variable need to be a clipboard? if it is in clipboard how to reference it? i.e if i have two or three formats stored in a clipboard how to select and paste the second one?
once i store this in a variable, i will paste in new sheets(about 50 of them) so that all these new sheets have same format as the template.
what is the easiest to do that?
View 1 Replies
View Related
Oct 30, 2012
I have code which is convert some range of excel to Png and store to my local drive. This code is working fine.
I have created web site on my intranet and the data store on server, now i use this code and pickup image from my local storage i.e. from "C" Drive and paste in to service folder, bcoz i uploaded report as a Image and it timely refreshed and changed
Is there any way so this code directly store to service folder, if i can use IP address or HTTP path
I had tried with HTTP path but it is not working.
My server path is [URL] .......
OR
Is there any way when local folder got image than automatically server folder also get same image...
Sub bah()
''' Set Range you want to export to file
Dim rgExp As Range: Set rgExp = Range("B2:C6")
''' Copy range as picture onto Clipboard
rgExp.CopyPicture Appearance:=xlScreen, format:=xlBitmap
''' Create an empty chart with exact size of range copied
[Code] .........
View 9 Replies
View Related
Jan 31, 2013
What I am trying to do is during a loop operation, which subtotals variable ranges, I want to store the locations of the cells that it puts the sum function into. I.E. if based on criteria it determines that range E4:E12 is summed into E13, I need to save E13 to use in a formula once I'm out of the loop. I don't know how many instances it will find and there's the possibility in the future that not only will the number of instances increase, but the location could always be different as well. Any way to do this without a million lines of code.
View 7 Replies
View Related
Feb 4, 2014
The problem with the code below is that it re-prompts the input box for each worksheet that the macro runs through.
What I would like to happen is the Input box appear at the begin, and that value is stored and then the same value pasted into A2 on each worksheet.
Code:
Dim ws As Worksheet
For Each ws In Worksheets
Rows("1:1").Insert Shift:=xlDown
[Code]....
View 2 Replies
View Related