Fill down part of macro does not work when only 1 or 2 rows of data

[CiaoKarina]

Hi! I'm sure this is something simple, its just that I am new to macros and don't completely understand everything I've found online!

I have a macro that is working great for me UNLESS I only have 1 or 2 rows of data.
First, let me explain my goal: In this example, Joe Johnson has 2 policies and I need his employee information on every line associated with him, so I need to "fill down" until the next value is found.

There are other things happening in the macro, but what I ultimately end up with is new B and C columns. B is concatenating the last 4 of A and the first 2 of what is now D.
Then I am using the "fill down" part of the macro to get a completed column C.

The error I get when there are only 1 or 2 rows is "AutoFill method of Range class failed". I can get around this by creating different macros for each but I'd rather understand what I'm doing wrong. There will never be a set number of rows. First time attaching mini-sheets so hopefully doing this correctly!
Thanks!
K

SAMPLEFILE.xlsm
	A	B	C	D	E	F	G
1	PayorNumber	Payor Name	Policy Number	Type	Deduction Amount	Total Due	Amount Remitted
2	XXXXXX1234	SMITH,ELIZABETH	1234566	GU	105.92	105.92	105.92
3	XXXXXX5678	JONES,MICHAEL	1234567	GU	110.48	110.48	110.48
4	XXXXXX1111	JOHNSON,JOE	1234568	GU	131.18	131.18	131.18
5			1234569	GU	22.46	22.46	22.46
6				TOTAL	153.64	153.64	153.64
7	XXXXXX2222	ALVAREZ,MARIA	876543	Cancer	24.9	24.9	24.9
8	XXXXXX3333	SIMPSON,JESSE	8765432	Cancer	24.36	24.36	24.36
Allstate Bill

SAMPLEFILE.xlsm
	A	B	C	D	E	F	G
1	PayorNumber	Payor Name	Policy Number	Type	Deduction Amount	Total Due	Amount Remitted
2	XXXXXX1234	SMITH,ELIZABETH	1234566	GU	105.92	105.92	105.92
3	XXXXXX5678	JONES,MICHAEL	1234567	GU	110.48	110.48	110.48
4
Allstate 2 people

SAMPLEFILE.xlsm
	A	B	C	D	E	F	G	H
1	PayorNumber	Payor Name	Policy Number	Type	Deduction Amount	Total Due	Amount Remitted	Reason
2	XXXXXX1234	SMITH,ELIZABETH	1234566	GU	105.92	105.92	105.92
3
Allstate 1 person

 

[bebo021999]

It can be solved via simple formula

Book1
	A	B	C	D	E	F	G	H
1	PayorNumber		Payor Name	Policy Number	Type	Deduction Amount	Total Due	Amount Remitted
2	XXXXXX1234	1234SM	SMITH,ELIZABETH	1234566	GU	105.92	105.92	105.92
3	XXXXXX5678	5678JO	JONES,MICHAEL	1234567	GU	110.48	110.48	110.48
4	XXXXXX1111	1111JO	JOHNSON,JOE	1234568	GU	131.18	131.18	131.18
5		1111JO		1234569	GU	22.46	22.46	22.46
6		1111JO			TOTAL	153.64	153.64	153.64
7	XXXXXX2222	2222AL	ALVAREZ,MARIA	876543	Cancer	24.9	24.9	24.9
8	XXXXXX3333	3333SI	SIMPSON,JESSE	8765432	Cancer	24.36	24.36	24.36
9
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=RIGHT(LOOKUP(2,1/($A$1:A2<>""),$A$1:A2),4)&LEFT(LOOKUP(2,1/($C$1:C2<>""),$C$1:C2),2)

 

[CiaoKarina]

Thanks! So first, thanks for simplifying my two step process into one! It worked for the sheet with 2 rows and more than 2 but I still get this error on the sheet with only row.

 

[bebo021999]

Why RC[-1]? in my origin formula, B2 lookup for range A1:A2
May be RC only? try it.
If it does not work, try to show results after coding and hightlight the wrong results.

 

[CiaoKarina]

Honestly, I am not sure. It doesn't seem to be a problem with the formula itself. When I paste your formula into the spreadsheet it works in all 3 sheets. When I record the macro and paste it in, it makes it the RC[-1] (?)
What doesn't work is when I change the fill down range from B2:B8 (which is what is saved in the macro when I double click the corner of B2 to fill down).
Since I will never have a set number of rows I am looking to fill down as long as I have a value in column H.

The results are correct, but it stops the macro.

 

[CiaoKarina]

Another question...I created the macros on a MAC version of Excel and this new formula is not working for users I shared it with who are running Windows. It immediately stops and debugger goes to the formula. Any idea what might be different? Thanks!