Skipping blank cells using =OFFSET and a macro that decides how many rows to offset

[gaberen]

Hi! I'm trying to create a macro that'll help me change how many rows to offset based on if it detects a blank cell or not.
First I'll show you what I'm trying to get at.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Data					Intended List				First Attempt				Second Attempt w/out VBA
2	No	Drink Combo	Fruits	Score		No	Drink Combo	Score		No	Drink Combo	Score		No	Drink Combo	Score
3	1	A	Banana			1	A	50		1	A	0		1	A	0
4			Grape	50		2	B	80		2	B	0		2	B	0
5						3	C	60		3	C	60		3	C	60
6	2	B	Banana			4	D	10		4	D	0		4	D	0
7			Grape
8			Apple	80										Intended Second Attempt w/ VBA Script
9														No	Drink Combo	Score
10	3	C	Strawberry	60										1	A	50
11														2	B	80
12	4	D	Blueberry											3	C	60
13			Raspberry	10										4	D	10
Sheet1

Cell Formulas
Range	Formula
K3:K6	K3	=VLOOKUP($J3, A:B, 2, FALSE)
L3:L6	L3	=VLOOKUP($J3, A:D, 4, FALSE)
O3:O6	O3	=VLOOKUP($J3, A:B, 2, FALSE)
P12,P3:P6	P3	=OFFSET(INDEX(D:D, MATCH(N3, A:A, 0)), 0, 0)
O10:O13	O10	=VLOOKUP($J3, A:B, 2, FALSE)
P10,P13	P10	=OFFSET(INDEX(D:D, MATCH(N10, A:A, 0)), 1, 0)
P11	P11	=OFFSET(INDEX(D:D, MATCH(N11, A:A, 0)), 2, 0)

And then here's my vba code that I'm having trouble with.
NOTE: I selected a cell with a "0" in it first before starting the macro

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.Select
      
    Dim OffsetNum As Integer
    OffsetNum = 0
    
    Do While ActiveCell = "0"
        OffsetNum = OffsetNum + 1
    Loop
    
    ActiveCell.Value = "=OFFSET(INDEX(D:D; MATCH(N3; A:A; 0)); " & OffsetNum & "; 0)"

End Sub

My first problem is that I get the error "overflow" from

Do While ActiveCell = "0"
        OffsetNum = OffsetNum + 1
    Loop

The order I'm expecting the code will go is:
Code detects that the ActiveCell is 0 -> OffsetNum (number of rows to be offset downwards) is increased by 1 -> if ActiveCell is still 0 then -> OffsetNum is increased by 1 again -> repeat until ActiveCell is no longer 0

My second problem is that I get the error "application-defined or object-defined error" from

    ActiveCell.Value = "=OFFSET(INDEX(D:D; MATCH(N3; A:A; 0)); " & OffsetNum & "; 0)"

It seems like the error is because the cell contains a string of more than 911 characters? I thought that it might be because my OffsetNum loop never actually stopped. But then I tried removing that loop and I still got the same error.

Any help and/or if anyone could help point me to the right way and what to learn I'd super appreciate it. Thanks for reading all the way here!

 

[bebo021999]

Solution with formula:

Book1
	A	B	C	D	E	F	G	H
1	Data					Intended List
2	No	Drink Combo	Fruits	Score		No	Drink Combo	Score
3	1	A	Banana			1	A	50
4			Grape	50		2	B	80
5						3	C	60
6	2	B	Banana			4	D	10
7			Grape
8			Apple	80
9
10	3	C	Strawberry	60
11
12	4	D	Blueberry
13			Raspberry	10
Sheet1

Cell Formulas
Range	Formula
F3:F13	F3	=IF(MAX($F$2:F2)+1<=MAX($A:$A),MAX($F$2:F2)+1,"")
G3:G13	G3	=IF(F3="","",VLOOKUP(F3,$A$3:$D$13,2,0))
H3:H13	H3	=IF(F3="","",IFERROR(SUM(INDEX(INDEX(D:D,MATCH(F3+1,A:A,0)-1):$D$3,)),SUM(D:D))-SUM($H$2:H2))

 

[gaberen]

Solution with formula:

Book1
	A	B	C	D	E	F	G	H
1	Data					Intended List
2	No	Drink Combo	Fruits	Score		No	Drink Combo	Score
3	1	A	Banana			1	A	50
4			Grape	50		2	B	80
5						3	C	60
6	2	B	Banana			4	D	10
7			Grape
8			Apple	80
9
10	3	C	Strawberry	60
11
12	4	D	Blueberry
13			Raspberry	10
Sheet1

Cell Formulas
Range	Formula
F3:F13	F3	=IF(MAX($F$2:F2)+1<=MAX($A:$A),MAX($F$2:F2)+1,"")
G3:G13	G3	=IF(F3="","",VLOOKUP(F3,$A$3:$D$13,2,0))
H3:H13	H3	=IF(F3="","",IFERROR(SUM(INDEX(INDEX(D:D,MATCH(F3+1,A:A,0)-1):$D$3,)),SUM(D:D))-SUM($H$2:H2))

oh my god I don't know what these formulas mean yet but I'm amazed they really work!! Thank you so much!!! I'll try to learn the functions you used here.