Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to get keys from collection
I have a dictionary with 200+ keys and picture objects in it.
Each key is a R1C1 style address (i.e. "R4C23"). Each picture object has a name that is both its type of picture, and the address of a cell which it is placed over (i.e. pic.name =LLR55C20). I need to be able to fill each cell with the address in each key of the dictionary with a formula. Whats the fastest way to do this? This... Dim aryKeys dim i as integer aryKeys = dict.keys For i to (dict.count - 1) range(aryKeys(i)).formulaR1C1 = "X" Next ....Wouldnt work because the key is in R1C1 style and range takes A1 style, right? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fastest way to get keys from collection
Not sure I follow, but you may be able to use this to convert the R4C23 to
$W$4. Convertformula also has options to manipulate the absolute/relative reference. Check VBA help. If your strings look like LLR55C20, I would imagine you will need to find a way to split the picture name and the cell reference. Sub test2() x = "R4C23" Cells(1, 1).Value = Application.ConvertFormula(x, _ xlR1C1, xlA1) End Sub "Abe" wrote: I have a dictionary with 200+ keys and picture objects in it. Each key is a R1C1 style address (i.e. "R4C23"). Each picture object has a name that is both its type of picture, and the address of a cell which it is placed over (i.e. pic.name =LLR55C20). I need to be able to fill each cell with the address in each key of the dictionary with a formula. Whats the fastest way to do this? This... Dim aryKeys dim i as integer aryKeys = dict.keys For i to (dict.count - 1) range(aryKeys(i)).formulaR1C1 = "X" Next ....Wouldnt work because the key is in R1C1 style and range takes A1 style, right? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use Alt keys + symbol keys to invoke a macro? | Excel Programming | |||
Sort collection string keys | Excel Programming | |||
Can't trap error when checking for keys not in Collection | Excel Programming | |||
Collection Object Keys | Excel Programming | |||
Fastest way to do this? | Excel Programming |