lookup offset

[white6174]

Heres what I want to do, I have a filtered list (that is generated with an array formula) in column A. Now in column C I want the same list but with some space's between(the number of space's is in column B, its figuired using a count if based on other criteria)

thanks steve w

Book1.htm
	A	B	C	D
4
5	1010A	4	1010A
6	1020B	3
7	1030B	1
8
9
10			1020B
11
12
13
14			1030B
15
16
Sheet1

This message was edited by white6174 on 2002-09-20 14:45

 

[Jay Petrulis]

Hi,

The following worked for me. Please test and report your results

<pre>Sub test()
Dim Rng1 As Range, Rng2 As Range
Dim UsedCell As Range, Counter As Long, i As Long
Dim MyArr1, MyArr2, MyArr3, MyArr4

Dim fn As WorksheetFunction
Set fn = Application.WorksheetFunction



With ActiveSheet

Set Rng1 = Intersect(.UsedRange, .Columns(1))
Set Rng2 = Rng1.Offset(0, 1)

For Each UsedCell In Rng1
If Not IsEmpty(UsedCell) Then
Counter = Counter + 1
If Counter = 1 Then
ReDim MyArr1(1 To Counter)
ReDim MyArr2(1 To Counter)
ReDim MyArr3(1 To Counter)
ReDim MyArr4(1 To Counter)
MyArr1(Counter) = UsedCell
MyArr2(Counter) = UsedCell.Offset(0, 1)
MyArr3(Counter) = UsedCell.Row
MyArr4(Counter) = 0
Else
ReDim Preserve MyArr1(1 To Counter)
ReDim Preserve MyArr2(1 To Counter)
ReDim Preserve MyArr3(1 To Counter)
ReDim Preserve MyArr4(1 To Counter)
MyArr1(Counter) = UsedCell
MyArr4(Counter) = fn.Sum(MyArr2) ' note the placement of this

MyArr2(Counter) = UsedCell.Offset(0, 1)
MyArr3(Counter) = UsedCell.Row

End If
End If
Next UsedCell

For i = 1 To Counter
.Cells(MyArr3(i) + MyArr4(i), 3) = MyArr1(i)
Next i

End With

End Sub</pre>

 

[Yogi Anand]

Hi White6174:

Here is a non-vba solution wherein I had to use some extra columns (the extra columns may eventually be hidden).

See the worksheet simulation ...

Book3
	A	B	C	D	E	F	G
1	1010A	4	5	0	1010A	1010A
2	1020B	3	4	6	1010A
3	1030A	1	2	10	1010A
4				12	1010A
5				12	1010A
6				12	1020B	1020B
7				12	1020B
8				12	1020B
9				12	1020B
10				12	1030A	1030A
11				12
12
Sheet2

</SPAN>

I am sure it will need some further tweaking.

Regards!

Yogi

 

[white6174]

yogi your formulas aren't showing

thanks steve w

 

[white6174]

never mind got it to work by opening it in frontpage and got it to work.

thanks alot just what I needed(I new it needed more columns, I thought it needed offset but I haven't used them much)

thanks again

steve w
This message was edited by white6174 on 2002-09-20 17:51