Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Offset (trying to resize an element within nested array)
I'm running into a problem with application.match, and I'm thinking it may be
because my comparison array is an array equivalent to a whole column in 2007 (1MM+ rows) so I want to shorten my array to only include the area with values. However, my attempts to shrink the nested array have so far been unsuccessful; Public AllRanges(1 To 10) As Variant Sub SetRange_DemoForNewsgroupPosting 'here is code without all the extra errorchecking and processing lines Set TempRange = Application.InputBox(Prompt:="Column?", Title:="Demo", Type:=8) Set PassRange = TempRange.Columns(1).EntireColumn AllRanges(2) = PassRange.Value End Sub Sub ShrinkMyArray Arr2Count = 528 Set AllRanges(2) = AllRanges(2)(1, 1).Offset(0, 0, Arr2Count, 1) End Sub For what it's worth, the reason I'm doing all this is because this next line of code works on workbookA on Excel2003, but when run on workbookB on Excel2007, it gives a runtime error 13, type mismatch: RowMatch = Application.Match(AllRanges(1)(i, 1), AllRanges(2), False) 'AllRanges(1)(i, 1) confirmed as an expected string, so the problem has to be with AllRanges(2). Using debug.print I confirmed that Allranges(2) is populated with the expected data... so my best guess is that the array size is just too large for application.match Thank you! Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Offset (trying to resize an element within nested array)
I would use the following:
from: AllRanges(1)(i, 1) to: AllRanges(1).offset(i, 1) AllRange will automtically return the 1st cell in the range. I'm not sure why you are using MATCH when you can use find instead. from RowMatch = Application.Match(AllRanges(1)(i, 1), AllRanges(2), False) to set c = Range(AllRanges(2)).find(what:=AllRanges(1).offset (i, 1), _ lookin:=xlvalues,lookat:=xlwhole) "ker_01" wrote: I'm running into a problem with application.match, and I'm thinking it may be because my comparison array is an array equivalent to a whole column in 2007 (1MM+ rows) so I want to shorten my array to only include the area with values. However, my attempts to shrink the nested array have so far been unsuccessful; Public AllRanges(1 To 10) As Variant Sub SetRange_DemoForNewsgroupPosting 'here is code without all the extra errorchecking and processing lines Set TempRange = Application.InputBox(Prompt:="Column?", Title:="Demo", Type:=8) Set PassRange = TempRange.Columns(1).EntireColumn AllRanges(2) = PassRange.Value End Sub Sub ShrinkMyArray Arr2Count = 528 Set AllRanges(2) = AllRanges(2)(1, 1).Offset(0, 0, Arr2Count, 1) End Sub For what it's worth, the reason I'm doing all this is because this next line of code works on workbookA on Excel2003, but when run on workbookB on Excel2007, it gives a runtime error 13, type mismatch: RowMatch = Application.Match(AllRanges(1)(i, 1), AllRanges(2), False) 'AllRanges(1)(i, 1) confirmed as an expected string, so the problem has to be with AllRanges(2). Using debug.print I confirmed that Allranges(2) is populated with the expected data... so my best guess is that the array size is just too large for application.match Thank you! Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rules for element-by-element product in array multiplication | Excel Programming | |||
Resize offset | Excel Programming | |||
using .resize or an offset | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming |