putting the address of all the cells in seperate items in an array

[christhedonstar]

Imagine I have selection A1:C1
with values 1, 2 and 3 respectively

If I write the code

dim myAddresses as variant
dim myValues as variant

myAddresses = selection.address
myValues = selection.values

myAddresses will be a string A1:C1
myValues will be an array with the following values 1, 2, and 3

Is it possible (quick way) to make myAddresses match up by being an array and having values A1, B1, C1?

Thanks,

Chris

 

[SkipVought]

Hi,

And what would be the purpose of doing that?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

I want to store the formulae in one array and the corresponding in another array. Trying to keep track of formulae changes. Doing it cell by cell takes a bit long (as you need to read a cell move to next one read the next etc. I'm using for each cell in usedrange) so I thought doing it memory may be much quicker. I can build up the addresses.

Do you know a quick way to get the addresses into an array?

 

[SkipVought]

You really hardly ever need to use individual range addresses. In your example, there is not advantage to having the individual addresses, if you have the range reference defined.

What does, "Trying to keep track of formulae changes" mean? I can't see how manipulating an array is any faster than manipulating a range collection.

Dim i As Integer
For i = 1 To Range("A1:C1").Count
  MsgBox Range("A1:C1")(i).Address
Next

But, again, I would never use addresses unless a function called for a range string, and it's always a RANGE, not individual cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

I think going through a variant array is quicker than going through a range... will find out tomorrowish...

 

[TonyJollans]

I agree with Skip - you do not want to be using addresses in that way at all - they would be wrong the minute the user added or deleted a cell or a row or column. However, if you know the address of the top left cell in the range then, effectively, you have the address of the cell corresponding to each entry in the Values array, by simple offsetting.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[combo]

A bit off topic, but after 'myValues = selection.values' you will get rather syntax error instead an array of values. Use

myValues = selection

to get 2D array of values.

combo

 

[christhedonstar]

Yeah the row and column deletions would likely mean functionality wouldn't change (unless there were some hardcoded references in the code that didn't refer to range names)...

Anyway yes I probably can write something that creates the address by knowing the the top left cell address, which is what I was going to do to test if I can get a speed increase...

Thanks Combo, it was a typo, shouldn't have the s there...

Haven't got round to writing this yet, but will let all know what I find out.

 

[SkipVought]

So what's the problem related to speed? How big is your range and how long does it take to process? Is there other stuff that could be causing delay?

Have you turned off ScreenUpdating? Are you using Activate & Select methods?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

...it was a typo, shouldn't have the s there...

Actually, there shouldn't be the whole 'value' (otherwise you will get only the value in first cell).

combo

 

[christhedonstar]

SkipVought I'm checking which cells have a formulae and adding them to a dictionary if it has one together with the address as a key. No activate and select methods just a for each loop through worksheets, for each cell in usedrange on the worksheet.

I should be looking at this either later today or tomorrow and will update the group with my findings.

Combo try this:

Sub test()
Dim a As Variant

a = Selection.Value

End Sub

PS I'm using Excel 2002.

 

[combo]

Obviously, you are right .

combo

 

[christhedonstar]

So I used the following to create the addresses unfortunately its slow 20 seconds to create all the variant arrays I need.

'Taken from the Web
'

http://www.freevbcode.com/ShowCode.asp?ID=4303

'04 Dec 2008
Function ColumnLetter(ByVal ColumnNumber As Integer) As String
If ColumnNumber > 26 Then

' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter

' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')

ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function

On the other hand 7 second to do the population which is 11 seconds faster than doing the read from the sheet! So it is significantly quicker to work with variant arrays rather than doing for each cell in range way of doing things... If only there was a quick way to populate the array with the addresses....

 

[christhedonstar]

Actually creating the address variant takes about the same time as going through each cell in the range