Get Address Of Array Variable?
May 13, 2013
Code:
Dim MyArray as Variant
Dim Address
MyArray = Range([a1], [b10])
For varRow = 1 To UBound(MyArray, 1)
' I want to know the current address of the cell right here,
' For instance I want to know I'm working with A1 right here
' Is that possible?
Next I guess my question is how do I find out the originating address of the cell I'm working with in an array. I need to check the font color of A1,B1,C1 etc while working in that range and I have no idea how to access it.
View 1 Replies
ADVERTISEMENT
May 2, 2013
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.
View 9 Replies
View Related
Jul 14, 2006
I need to select a variable number of cells on a line. I know Range("A2:c2") will do exactly what I need EXCEPT i need to use variables to do it. Is this possible, and if so how.
View 5 Replies
View Related
Sep 12, 2006
I would like to record the address of the last set of cells that data was input into to a variable so that a user can choose to delete the last entry. An 'Undo' button really.
What I have is a user form that writes different materials to thier respective sheets in the database. (Material1, Material2, etc.) Some materials have a different number of variables (some have a width and some don't, but all have a quantity.)
This is the code I have for adding the material to the database (each material has it's own button with material specific code.)
Private Sub AddToMaterial1_Click()
Set c = Worksheets("Material1").Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False
c.Value = Me.Material1Quantity.Value
c.Offset(0, 1).Value = Me.Material1Description.Value
c.Offset(0, 2).Value = Me.Material1Length.Value
Dim lastenty1
lastentry1 = c.Address
Dim lastentry2
lastentry2 = c.Offset(0, 1).Address
Dim lastentry3
lastentry3 = c.Offset(0, 2).Address
Dim lastentry4
lastentry4 = c.Offset(0, 3).Address
Dim lastentry5
lastentry5 = vbNullString
Dim lastentry6
lastentry6 = vbNullString
Application.ScreenUpdating = True
End Sub
The following code is what I am trying to do for a single button to clear the last entry to the database.
Private Sub RemoveLastEntry_Click()
Range(lastentry1).ClearContents
Range(lastentry2).ClearContents
Range(lastentry3).ClearContents '(There is always at least 3 cells to clear)
If lastentry4 = nullstring Then Exit Sub
Range(lastentry4).ClearContents
If lastentry5 = nullstring Then Exit Sub
Range(lastentry5).ClearContents
If lastentry6 = nullstring Then Exit Sub
Range(lastentry6).ClearContents
End Sub
View 9 Replies
View Related
Feb 20, 2008
I have created a work diary in Excel which is built up in half hour blocks. I am able to create my appointments (usually one or two hour blocks) by selecting a group of cells and running a macro which merges the cells, colours them and puts a border round them.
I now want to be able to delete individual appointments by selecting the appropriate appointment and running a macro to copy and paste from the same range of cells in another worksheet (which is effectively my blank master diary page).
I have tried work out what code I need to put the range of the current selection into a variable in VBA but I'm not having any luck.
View 2 Replies
View Related
Oct 5, 2013
In the following example, I need to create a formula to identify the cell address containing the string value "X"
BCDEFXABCDEFXABCDEF
Assuming this was row A, the formula needs to return $A$6 (or A6).
View 7 Replies
View Related
Apr 1, 2009
Here is my code
Dim LastCell As Range
Set LastCell = Cells(EndRow, LastCol).Address
I get a "Object Required" when this I try to set the address. I know this is easy.
View 9 Replies
View Related
May 1, 2006
I have a table in which I have a "patient" column and a "page" column. The macro searches for a patient's number, then looks if this patient has the page "900.--". A patient may have more than one page, which will result in many rows with the same patient number. So far, my macro uses the search function to find a patient number, then compares the cell next to the active cell to see if it contains the page "900.--". If not, then my macro searches for the next patient and so on until the page is found and noted into another workbook or none is found.
In order to stop the loop, I am trying to store the address of the first cell found into a variable "rFirstCell" so that it can later on be compared to another variable, "rSecondCell", which represent the active cell. When both are the same, it means all the available search results have been tested and the loop should stop.
Sub testing()
Dim rRng As Range, rFirstCell As Range, rSecondCell As Range
Set rRng = Worksheets("Overview").[a1]
Dim sDeath As String
sDeath = "death"
ActiveSheet.AutoFilterMode = False
If LCase(rRng(2, 15).Value) = "x" Then
If LCase(rRng(2, 9).Value) = sDeath Then
Workbooks("DM Endpoint pages_test.xls").Activate
Range("A1").Select...........................
View 4 Replies
View Related
Apr 10, 2007
when i am doing the coding
may i know if there is a way to replace the cell address $A9 below: ...
View 3 Replies
View Related
Jun 30, 2006
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
View 4 Replies
View Related
Apr 2, 2014
I have a large table of data sorted by date and I need to get the last value for a given variable. in certain instances using LOOKUP(9.999999E+307,sheet1!A:A) works but not when I'm trying to get values for past dates
Her is an example table:
Date
Month Value
Variable
1/2/14
1
11
[Code]...
If i need to find the last value for the month of February, in this case 514, what combinations of formulas should I use? I feel like this should be easier than I making it out to be.
View 5 Replies
View Related
Jul 25, 2014
Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:
=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),
VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))
Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.
View 10 Replies
View Related
Apr 3, 2009
I've successfully copied the array of equations using the VBA that Pjoaquin enlightened me with from my last thread. The outcome was Sheet2!A2:O2 being successfully populated with the equations from my first sheet... but here comes the problem: I'm looking to autofill A2:O2 down to the last record in Column P. But the number of records in this table is varable.
View 2 Replies
View Related
May 12, 2009
I am trying to change the variable value in my following code through array.
What I want is that both the statements
Debug.Print testarray(0) & "........" & testarray(1) & "......." & testarray(2)
Debug.Print custname & "........" & custaccount & "......." & worthcredit & vbCrLf & vbCrLf
should deliver me the same values i.e changedname 123456 and true
for testarray(0),testarray(1),testarray(2) i am getting the values but I am not able to change the variable values for custname ,custaccount and worthcredit, although I am accessing the same elements.
Here is full ....
View 12 Replies
View Related
May 19, 2009
My question is about assigning an entire array to a single variable. In this case, I want to assign an entire array to one element of another array.
View 6 Replies
View Related
Aug 11, 2009
I have a variable array, that is, the first cell of the array is variable and the last cell is variable. I have dimmed the first cell , "firstcell" as a range. I have dimmed the last cell , "lastcell" as a range. I'd like to sort the array but first I have to select all cells in the array. Need the proper syntax to select all cells between "firstcell" and "lastcell" in my macro.
View 2 Replies
View Related
May 15, 2008
how to do is the assignment of GoodArray1 to CurrentArrayToUse, i.e. "CurrentArrayToUse = GoodArray1" below:
Global NextArrayToUse()
Global CurrentArrayToUse()
Global PreviousArrayToErase()
Global GoodArray1(), GoodArray2(), [etc]
Global CurrentGuessNumber As Integer
[bunch of code, part of which assigns a number to CurrentGuessNumber, then the following...]
Select Case CurrentGuessNumber
Case 1
CurrentArrayToUse = GoodArray1
NextArrayToUse = GoodArray2
Case 2
CurrentArrayToUse = GoodArray2
NextArrayToUse = GoodArray3
PreviousArrayToErase = GoodArray1
ReDim PreviousArrayToErase(0, 0)
View 9 Replies
View Related
Jan 7, 2009
I am running an array formula which is working fine except that I now need to add a further condition: that a one of a number of values in cells C1:C8 is found in range $a1:$A500. I've tried Or with comma separation and with * separations but nothing seems to work.
Can anyone advise me of the syntax?
View 9 Replies
View Related
Dec 17, 2008
I'm trying to populate a worksheet using arrays.
View 14 Replies
View Related
May 7, 2009
I need to create an array with a variable as it size For instance:
View 2 Replies
View Related
Jan 5, 2010
I created an Index/Match array forumula in the worksheet. It works.
View 2 Replies
View Related
Mar 14, 2012
Is it at all possible to refer to a array that may change in a formula?
For example I need to use a Vlookup formula, however the table array will change depending on the value of another cell.
I need the user to be able to select the column heading that the lookup should work off from a drop down list. So if the user selects column heading C, the array should start from column C though it will always end at column Z. If the user selects column heading Y the array would be Y:Z.
View 7 Replies
View Related
Apr 10, 2007
I am trying to do is to build an array from a series of cells that may or may not contain text.
For example cells A1:A6 may be equal to red, orange, yellow, green, blue and purple,
but each cell may also be blank.
So I might also have blank, blank, yellow, blank, blue, purple.
Is there a way I can make an array of just {yellow, blue, purple}? and then the next time it may be {red, blue}
Or can I index only cells that have text?
In the end, I just need a way to make a selection from only those cells that have text.
View 9 Replies
View Related
Nov 30, 2008
I'm trying to use arrays in a macro!
Here's my
Sub ArrayTest()
'
' ArrayTest Macro
'
Dim Array1() As Variant, Array2() As Variant
Dim R As Integer, ilR1 As Integer, iLR2 As Integer
ilR1 = Cells(Rows.Count, 1).End(xlUp).Row
iLR2 = Cells(Rows.Count, 7).End(xlUp).Row
ReDim Array1(1 To ilR1)
ReDim Array2(1 To iLR2)
For R = 1 To ilR1
Array1(R) = Cells(R, 1) & Cells(R, 2) & Cells(R, 3)
Next R
For R = 1 To iLR2
Array2(R) = Cells(R, 7) & Cells(R, 8) & Cells(R, 9)
Next R
For R = 2 To UBound(Array1)
If WorksheetFunction.CountIf(?????, Array1(R)) > 0 Then Cells(R, 4) = "old" Else Cells(R, 4) = "new"
Next R
'
End Sub
Basically, the macro concatenates the content of three columns from two different tables into two arrays. Array1 contains the current stuff and Array2, the old one. If I CountIf items from Array1 into the content of Array2, I should get what's new (count 0).
Now, ? in the code should refer to the content of Array2. How do I do that?
The errors I get are 424 object required [Array(iLR2)], 1004 method 'Range' of object '_Global' failed [Range(Array2) or Range(Array2(2), Array2(iLR2))], or type mismatched [Array2].
View 9 Replies
View Related
Mar 6, 2010
How can I make a dynamic array public?
I have to Dim it so it stays valid only inside the sub.
Public MyVar()
Sub test1()
Dim MyVar()
ReDim MyVar(1 To 4)
For x = 1 To 4
MyVar(x) = "ffffff"
Next x
End Sub
Sub test2()
For x = 1 To 4
Range("A" & x) = MyVar(x)
Next x
End Sub
Error I get is 'MyVar(x) = Subscript out of range'
View 9 Replies
View Related
Jul 8, 2006
=VLOOKUP($B$54,'R:OperationsPerformance ReportingSales ReportsFTW North - INT2006Daily7 July7-02-2006[Sales TL Base Report.xls]MAXimize Summary'!$A:$BA,E$3,FALSE)
I would like to make date in the table array (07 July7-02-2006) a variable, but keep getting an #NA with my limited experience. From what I can gather the single quotes are causing the problem?
View 9 Replies
View Related
Apr 25, 2007
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
Next i
End Sub
View 3 Replies
View Related
Oct 2, 2007
I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?
1 click - a1 = ..
2 click - a2 = ..
....
n click - an = ..
If not possible - which way to search a solution?
View 5 Replies
View Related
Jun 14, 2008
I've created a userform that uses parallel arrays to display strings that another macro pulls from a worksheet and the 2nd array holds strings of what the user types in. I've uploaded a workbook with the userform in it.
Part 1:
I want to pass the 1st array (pSource) in during the initialization of the array and I want to send back the 2nd array (pUser) from the submit button, but I have no idea how to work it. I've tried a couple things from older posts in the forums, but had no luck with them.
Part 2:
The arrays will be the same size, but the size isn't a constant. Is there any way to change the size of an array? I've been using 0 to 3 for testing the rest of the userform, but for the final product, I'll be passing an array in of a variable size.
Private pSource(3) As String 'Modify to work with variable size
Private pUser(3) As String 'Modify to work with variable size
View 3 Replies
View Related
Aug 18, 2009
How do I make a column array variable within a function? I've done nested vlookups to get a column number, but the function I'm using doesn't use a "number", it uses a column aray. '=AVERAGEIFS(L:L,K:K,">="&B$2,K:K,"<="&B$3). The "L:L" needs to be moved according to Market, (or column choosen).
View 2 Replies
View Related
