Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer range to array etc.
I am trying to use the following code to:
Transfer a 2 column range into "vertical arrays" two arrays Find each element of the 1st array in a worksheet range Print the corresponding element in the second array into a cell offset from the range My code is not working. I am getting an error that I can't track down. Sub Honolulu() Dim intPos As Integer Dim i As Integer x = Range("BS9:BS300") y = Range("BT9:BT300") i = 1 For i = 1 To UBound(x, 1) intPos = Application.Match(x(i, 1), ActiveSheet.Columns(2)) If Not IsError(intPos) Then With ActiveSheet .Cells(intPos, 11) = y(i) End With End If Next i End Sub Thanks is advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer range to array etc.
This works - but not sure if you get the results you expect?
Sub Honolulu() Dim intPos As Integer Dim i As Integer, x As Variant, y As Variant x = Range("BS9:BS300") y = Range("BT9:BT300") i = 1 For i = 1 To UBound(x) intPos = Application.Match(x(i, 1), ActiveSheet.Columns(2)) If Not IsError(intPos) Then With ActiveSheet .Cells(intPos, 11) = y(i, 1) End With End If Next i End Sub -- Cheers Nigel "AD108" wrote in message ... I am trying to use the following code to: Transfer a 2 column range into "vertical arrays" two arrays Find each element of the 1st array in a worksheet range Print the corresponding element in the second array into a cell offset from the range My code is not working. I am getting an error that I can't track down. Sub Honolulu() Dim intPos As Integer Dim i As Integer x = Range("BS9:BS300") y = Range("BT9:BT300") i = 1 For i = 1 To UBound(x, 1) intPos = Application.Match(x(i, 1), ActiveSheet.Columns(2)) If Not IsError(intPos) Then With ActiveSheet .Cells(intPos, 11) = y(i) End With End If Next i End Sub Thanks is advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer range to array etc.
The variables x and y are not shown dimmed, but assuming variants.
You should be getting a subscript out of range error on the line : ..Cells(intPos, 11) = y(i) because the array is 2D. You have the correct syntax for x, so the same for y However, you do not need 2 arrays ; you can dump the 2 columns in 1 array x = Range("BS9:BT300") and .Cells(intPos, 11) = x(i,2) NickHK "AD108" wrote in message ... I am trying to use the following code to: Transfer a 2 column range into "vertical arrays" two arrays Find each element of the 1st array in a worksheet range Print the corresponding element in the second array into a cell offset from the range My code is not working. I am getting an error that I can't track down. Sub Honolulu() Dim intPos As Integer Dim i As Integer x = Range("BS9:BS300") y = Range("BT9:BT300") i = 1 For i = 1 To UBound(x, 1) intPos = Application.Match(x(i, 1), ActiveSheet.Columns(2)) If Not IsError(intPos) Then With ActiveSheet .Cells(intPos, 11) = y(i) End With End If Next i End Sub Thanks is advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer range to array etc.
AD108 wrote:
My code is not working. I am getting an error that I can't track down. Sub Honolulu() Dim intPos As Integer Dim i As Integer x = Range("BS9:BS300") y = Range("BT9:BT300") i = 1 For i = 1 To UBound(x, 1) intPos = Application.Match(x(i, 1), ActiveSheet.Columns(2)) If Not IsError(intPos) Then With ActiveSheet .Cells(intPos, 11) = y(i) End With End If Next i End Sub Several errors: #1 -- Your IsError() statement doesn't do anything -- If intPos does have an error, your Application.Match statement will terminate the routine. #2 -- As is, the only way the Match routine can have an error is if every cell in the 2nd column is empty. You have to add the 3rd parameter to tell it you want an exact match. #3 -- You're referring to y() as a 1-dimensional array. It's 2-dimensional If I understood your description correctly, I think what you want is: Sub Honolulu() Dim intPos As Integer Dim i As Integer x = Range("BS9:BS15") y = Range("BT9:BT15") For i = 1 To UBound(x, 1) If Not IsError(Application.Match(x(i,1),ActiveSheet.Colum ns(2),0)) Then intPos = Application.Match(x(i, 1),ActiveSheet.Columns(2),0) ActiveSheet.Cells(intPos, 11) = y(i, 1) End If Next i End Sub I'm not sure why you couldn't "track down" the errors. They were either flagged by VBA during line by line execution or by watching the value of "intPos" in the watch window. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfer range to array etc.
Thanks,
That worked "Nigel" wrote in message ... This works - but not sure if you get the results you expect? Sub Honolulu() Dim intPos As Integer Dim i As Integer, x As Variant, y As Variant x = Range("BS9:BS300") y = Range("BT9:BT300") i = 1 For i = 1 To UBound(x) intPos = Application.Match(x(i, 1), ActiveSheet.Columns(2)) If Not IsError(intPos) Then With ActiveSheet .Cells(intPos, 11) = y(i, 1) End With End If Next i End Sub -- Cheers Nigel "AD108" wrote in message ... I am trying to use the following code to: Transfer a 2 column range into "vertical arrays" two arrays Find each element of the 1st array in a worksheet range Print the corresponding element in the second array into a cell offset from the range My code is not working. I am getting an error that I can't track down. Sub Honolulu() Dim intPos As Integer Dim i As Integer x = Range("BS9:BS300") y = Range("BT9:BT300") i = 1 For i = 1 To UBound(x, 1) intPos = Application.Match(x(i, 1), ActiveSheet.Columns(2)) If Not IsError(intPos) Then With ActiveSheet .Cells(intPos, 11) = y(i) End With End If Next i End Sub Thanks is advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to transfer specific range to another range ? | Excel Discussion (Misc queries) | |||
Named Range Transfer | Excel Discussion (Misc queries) | |||
VBA Array Transfer | Excel Programming | |||
transfer cell range | Excel Programming | |||
Transfer Range to Array | Excel Programming |