Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union method for Range Object
I am attempting to fill an array with a non-contiguous range. I have figured
out several ways to do this but each has its limitations. Using the following, the range object created has a single column (even though the reference includes multiple columns). Dim X_in As Range, X As Variant, i, j Set X_in = Application.Range(Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$ 6) X = X_in For j = 1 To UBound(X, 2) For i = 1 To UBound(X, 1) Cells(i, j + 10) = X(i, j) Next i Next j I have tried the Union method, areas, etc. Thanks, Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union method for Range Object
This works for skipping columns
Should work for skipping rows or skipping both rows and columns. (as long as the corners of the data are a rectangle and skipped rows and columns are the same for all - so this would work: Set X_in = Range("Sheet1!B2:C3,E2:F3,B6:C7,E6:F7") ) --------------------------- Dim X_in As Range, X As Variant Dim i As Long, j As Long Dim rngRow As Range, rngCol As Range Dim rw As Range, col As Range Dim ii As Long, jj As Long Set X_in = Range("Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$6") Set rngCol = Intersect(X_in(1).EntireRow, X_in.EntireColumn) Set rngRow = Intersect(X_in(1).EntireColumn, X_in.EntireRow) ReDim X(1 To rngRow.Count, 1 To rngCol.Count) 'Debug.Print rngRow.Address, rngCol.Address For Each cell In X_in i = 0 For Each rw In rngRow i = i + 1 If rw.Row = cell.Row Then ii = i Exit For End If Next j = 0 For Each col In rngCol j = j + 1 If col.Column = cell.Column Then jj = j End If Next X(ii, jj) = cell.Value Next cell For i = 1 To UBound(X, 1) For j = 1 To UBound(X, 2) sStr = sStr & X(i, j) & ", " Next sStr = sStr & vbNewLine Next Debug.Print sStr End Sub -- Regards, Tom Ogilvy "Chad" wrote in message ... I am attempting to fill an array with a non-contiguous range. I have figured out several ways to do this but each has its limitations. Using the following, the range object created has a single column (even though the reference includes multiple columns). Dim X_in As Range, X As Variant, i, j Set X_in = Application.Range(Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$ 6) X = X_in For j = 1 To UBound(X, 2) For i = 1 To UBound(X, 1) Cells(i, j + 10) = X(i, j) Next i Next j I have tried the Union method, areas, etc. Thanks, Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union method for Range Object
Thanks, Tom.
I am getting the range reference from a userform and the worksheet name is included with the reference. I am currently using string functions to extract the relevant reference. I was hoping there was a more elegant solution, but it seems that there is not. Chad "Tom Ogilvy" wrote: This works for skipping columns Should work for skipping rows or skipping both rows and columns. (as long as the corners of the data are a rectangle and skipped rows and columns are the same for all - so this would work: Set X_in = Range("Sheet1!B2:C3,E2:F3,B6:C7,E6:F7") ) --------------------------- Dim X_in As Range, X As Variant Dim i As Long, j As Long Dim rngRow As Range, rngCol As Range Dim rw As Range, col As Range Dim ii As Long, jj As Long Set X_in = Range("Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$6") Set rngCol = Intersect(X_in(1).EntireRow, X_in.EntireColumn) Set rngRow = Intersect(X_in(1).EntireColumn, X_in.EntireRow) ReDim X(1 To rngRow.Count, 1 To rngCol.Count) 'Debug.Print rngRow.Address, rngCol.Address For Each cell In X_in i = 0 For Each rw In rngRow i = i + 1 If rw.Row = cell.Row Then ii = i Exit For End If Next j = 0 For Each col In rngCol j = j + 1 If col.Column = cell.Column Then jj = j End If Next X(ii, jj) = cell.Value Next cell For i = 1 To UBound(X, 1) For j = 1 To UBound(X, 2) sStr = sStr & X(i, j) & ", " Next sStr = sStr & vbNewLine Next Debug.Print sStr End Sub -- Regards, Tom Ogilvy "Chad" wrote in message ... I am attempting to fill an array with a non-contiguous range. I have figured out several ways to do this but each has its limitations. Using the following, the range object created has a single column (even though the reference includes multiple columns). Dim X_in As Range, X As Variant, i, j Set X_in = Application.Range(Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$ 6) X = X_in For j = 1 To UBound(X, 2) For i = 1 To UBound(X, 1) Cells(i, j + 10) = X(i, j) Next i Next j I have tried the Union method, areas, etc. Thanks, Chad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union method for Range Object
More elegant than what? What do you mean be string functions to extract the
relevant reference? You didn't ask anything like that in your original post? What is the actual question. Guess that was a waste of time. -- Regards, Tom Ogilvy "Chad" wrote in message ... Thanks, Tom. I am getting the range reference from a userform and the worksheet name is included with the reference. I am currently using string functions to extract the relevant reference. I was hoping there was a more elegant solution, but it seems that there is not. Chad "Tom Ogilvy" wrote: This works for skipping columns Should work for skipping rows or skipping both rows and columns. (as long as the corners of the data are a rectangle and skipped rows and columns are the same for all - so this would work: Set X_in = Range("Sheet1!B2:C3,E2:F3,B6:C7,E6:F7") ) --------------------------- Dim X_in As Range, X As Variant Dim i As Long, j As Long Dim rngRow As Range, rngCol As Range Dim rw As Range, col As Range Dim ii As Long, jj As Long Set X_in = Range("Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$6") Set rngCol = Intersect(X_in(1).EntireRow, X_in.EntireColumn) Set rngRow = Intersect(X_in(1).EntireColumn, X_in.EntireRow) ReDim X(1 To rngRow.Count, 1 To rngCol.Count) 'Debug.Print rngRow.Address, rngCol.Address For Each cell In X_in i = 0 For Each rw In rngRow i = i + 1 If rw.Row = cell.Row Then ii = i Exit For End If Next j = 0 For Each col In rngCol j = j + 1 If col.Column = cell.Column Then jj = j End If Next X(ii, jj) = cell.Value Next cell For i = 1 To UBound(X, 1) For j = 1 To UBound(X, 2) sStr = sStr & X(i, j) & ", " Next sStr = sStr & vbNewLine Next Debug.Print sStr End Sub -- Regards, Tom Ogilvy "Chad" wrote in message ... I am attempting to fill an array with a non-contiguous range. I have figured out several ways to do this but each has its limitations. Using the following, the range object created has a single column (even though the reference includes multiple columns). Dim X_in As Range, X As Variant, i, j Set X_in = Application.Range(Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$ 6) X = X_in For j = 1 To UBound(X, 2) For i = 1 To UBound(X, 1) Cells(i, j + 10) = X(i, j) Next i Next j I have tried the Union method, areas, etc. Thanks, Chad |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union method for Range Object
I was looking for a way that did not involve a loop, etc. I thought perhaps
there was a simple solution that I was overlooking. I appreciate your answer and it was not my intention to insult you by implying that your solution is not elegant. By elegant, I mean simple. Perhaps your solution is as elegant as there can be--I don't know. You don't need to be so irascible. chad "Tom Ogilvy" wrote: More elegant than what? What do you mean be string functions to extract the relevant reference? You didn't ask anything like that in your original post? What is the actual question. Guess that was a waste of time. -- Regards, Tom Ogilvy "Chad" wrote in message ... Thanks, Tom. I am getting the range reference from a userform and the worksheet name is included with the reference. I am currently using string functions to extract the relevant reference. I was hoping there was a more elegant solution, but it seems that there is not. Chad "Tom Ogilvy" wrote: This works for skipping columns Should work for skipping rows or skipping both rows and columns. (as long as the corners of the data are a rectangle and skipped rows and columns are the same for all - so this would work: Set X_in = Range("Sheet1!B2:C3,E2:F3,B6:C7,E6:F7") ) --------------------------- Dim X_in As Range, X As Variant Dim i As Long, j As Long Dim rngRow As Range, rngCol As Range Dim rw As Range, col As Range Dim ii As Long, jj As Long Set X_in = Range("Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$6") Set rngCol = Intersect(X_in(1).EntireRow, X_in.EntireColumn) Set rngRow = Intersect(X_in(1).EntireColumn, X_in.EntireRow) ReDim X(1 To rngRow.Count, 1 To rngCol.Count) 'Debug.Print rngRow.Address, rngCol.Address For Each cell In X_in i = 0 For Each rw In rngRow i = i + 1 If rw.Row = cell.Row Then ii = i Exit For End If Next j = 0 For Each col In rngCol j = j + 1 If col.Column = cell.Column Then jj = j End If Next X(ii, jj) = cell.Value Next cell For i = 1 To UBound(X, 1) For j = 1 To UBound(X, 2) sStr = sStr & X(i, j) & ", " Next sStr = sStr & vbNewLine Next Debug.Print sStr End Sub -- Regards, Tom Ogilvy "Chad" wrote in message ... I am attempting to fill an array with a non-contiguous range. I have figured out several ways to do this but each has its limitations. Using the following, the range object created has a single column (even though the reference includes multiple columns). Dim X_in As Range, X As Variant, i, j Set X_in = Application.Range(Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$ 6) X = X_in For j = 1 To UBound(X, 2) For i = 1 To UBound(X, 1) Cells(i, j + 10) = X(i, j) Next i Next j I have tried the Union method, areas, etc. Thanks, Chad |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union method for Range Object
Nor do you need to be accusatory. It certainly is presumptuous for you to
assign anger to my response. Nothing farther from the truth. I was simply trying to find out what your actual question was. Your response seemed to have little relation to the original question - at least as I perceived them. original question: I am attempting to fill an array with a non-contiguous range followed by a statement saying you knew how to do it followed by code that didn't get the job done and didn't say anything about not looping. I your responser to where I provided code that filled the array properly you said: I am getting the range reference from a userform and the worksheet name is included with the reference. I am currently using string functions to extract the relevant reference. you then made a conclusion: I was hoping there was a more elegant solution, but it seems that there is not. Which to me doesn't seem relevant, but maybe its me. And yes, I did ask more elegant than what (after all, the topic seemded to have changed) - as it wasn't apparent what you meant - I certainly didn't take it as being aimed at me. so pardon me for asking. -- Regards, Tom Ogilvy "Chad" wrote in message ... I was looking for a way that did not involve a loop, etc. I thought perhaps there was a simple solution that I was overlooking. I appreciate your answer and it was not my intention to insult you by implying that your solution is not elegant. By elegant, I mean simple. Perhaps your solution is as elegant as there can be--I don't know. You don't need to be so irascible. chad "Tom Ogilvy" wrote: More elegant than what? What do you mean be string functions to extract the relevant reference? You didn't ask anything like that in your original post? What is the actual question. Guess that was a waste of time. -- Regards, Tom Ogilvy "Chad" wrote in message ... Thanks, Tom. I am getting the range reference from a userform and the worksheet name is included with the reference. I am currently using string functions to extract the relevant reference. I was hoping there was a more elegant solution, but it seems that there is not. Chad "Tom Ogilvy" wrote: This works for skipping columns Should work for skipping rows or skipping both rows and columns. (as long as the corners of the data are a rectangle and skipped rows and columns are the same for all - so this would work: Set X_in = Range("Sheet1!B2:C3,E2:F3,B6:C7,E6:F7") ) --------------------------- Dim X_in As Range, X As Variant Dim i As Long, j As Long Dim rngRow As Range, rngCol As Range Dim rw As Range, col As Range Dim ii As Long, jj As Long Set X_in = Range("Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$6") Set rngCol = Intersect(X_in(1).EntireRow, X_in.EntireColumn) Set rngRow = Intersect(X_in(1).EntireColumn, X_in.EntireRow) ReDim X(1 To rngRow.Count, 1 To rngCol.Count) 'Debug.Print rngRow.Address, rngCol.Address For Each cell In X_in i = 0 For Each rw In rngRow i = i + 1 If rw.Row = cell.Row Then ii = i Exit For End If Next j = 0 For Each col In rngCol j = j + 1 If col.Column = cell.Column Then jj = j End If Next X(ii, jj) = cell.Value Next cell For i = 1 To UBound(X, 1) For j = 1 To UBound(X, 2) sStr = sStr & X(i, j) & ", " Next sStr = sStr & vbNewLine Next Debug.Print sStr End Sub -- Regards, Tom Ogilvy "Chad" wrote in message ... I am attempting to fill an array with a non-contiguous range. I have figured out several ways to do this but each has its limitations. Using the following, the range object created has a single column (even though the reference includes multiple columns). Dim X_in As Range, X As Variant, i, j Set X_in = Application.Range(Sheet1!$A$1:$A$6,Sheet1!$C$1:$D$ 6) X = X_in For j = 1 To UBound(X, 2) For i = 1 To UBound(X, 1) Cells(i, j + 10) = X(i, j) Next i Next j I have tried the Union method, areas, etc. Thanks, Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method 'Range' of object '_global' failed | Excel Discussion (Misc queries) | |||
What does XLS VB Method 'Range of object' _Global failed mean? | Excel Programming | |||
Problem is using any method of Range Object | Excel Programming | |||
Method Range of object global failed | Excel Programming | |||
Method 'Range' of object '_Global' failed | Excel Programming |