Help with Loop

[Roller]

Hello all, I am looking for some guidance for performing a loop to essentially concatenate data from a range of cells into an output file. I have written code that writes (exports) a *.txt file for all data entered into worksheet. Each row of data is a separate check payment to various vendors so the data is very simple: Name, address, check amount, and data from the invoice. The limitation with what I've built is each row is a single payment record and the macro writes each row into the *.txt file in the same order it was input into the worksheet.

I want to expand the use of this worksheet to provide the users of this worksheet to add multiple invoices to a single payment. Functionally, the user just adds the necessary data in the corresponding columns for the data they are adding while leaving the other fields (i.e. name, address, amount) blank. If I use check number as the key field that links the records together, how can I perform a loop for to count the number of times the check number field is the same so I can use the loop to concatenate the data fields and then move on to the next payment. Each payment can have 0 additional records or up to 2400 (unlikely but that is the system max).

I have a constraint that all this data must be concatenated in the output file.

I cannot seem to identify how many additional records are present for a given payment. I plan to use this variable amount in the .offset( ) to get the correct cell reference. Thanks in advance for your consideration. ​

 

[DanteAmor]

Then you must explain how your data is, with the example data that I put in post #6 generate a file.

Use XL2BB tool to display an example data.

 

[Roller]

Hi Dante Amor - Sorry for the delayed response... (Shifting priorities at work)… Anyway, I have tried your code and am still getting a blank file when running your macro. Here is my data

INPAlternateCheckIssuance v060220.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N						T	U	V	W	X
1	Check No.	Payee Name 1	Payee Name 2 [optional]	Check Date	Amount	Account	Mail Method	Address Line1	Address Line2	City	State or Province	Zip	Country						Remit 1 EG: Invoice #	Remit 2 EG: Invoice Dt	Remit 3 EG: Inv Amount	Remit 4 EG: Discount	Remit 5 EG: Description
2	123456	ABC Company		5/28/2020	2,500.00	1851812345	US	1234 Street Dr	Suite 1A	Los Angeles	CA	90200	US						555-asdf2	05/01/20	1500.00	0.00	Smith
3	123456																		556-asdf2	05/08/20	500.00	0.00	Jenkins
4	123456																		557-asdf2	05/15/20	500.00	0.00	Roberts
5	123457	XYZ Company		5/28/2020	500.00	1851812345	US	333 Main St.	Rm 222b	Detroit	MI	48101	US						5648872	04/30/20	600.00	100.00	Requisition
6	123458	PQS Incorporated		5/28/2020	1,000.00	1851812345	US	P.O. Box 23456		Dallas	TX	66110	US						332110	04/30/20	200.00	0.00	Reimbursement
7	123458																		332087	03/31/20	800.00	0.00	Q1 941
CheckDataInput

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C7	Expression	=LEN($C2)<=ReferenceTab!$G$4	text	NO
L2:L7	Expression	=IF($N2="US",AND(ISBLANK($L2)=FALSE,LEN($L2)=2),LEN($L2)<=ReferenceTab!$G$13)	text	NO
N2:N7	Expression	=N2=LOOKUP(N2,CountryCode)	text	NO
M2:M7	Expression	=OR(LEN(M2)=5,LEN(M2)=9,AND(LEN(M2)=10,IFERROR(FIND("-",M2,1)=6,LEN(M2))))	text	NO
K2:K7	Expression	=AND(ISBLANK(K2)=FALSE,LEN(K2)<=ReferenceTab!$G$12)	text	NO
J2:J7	Expression	=AND(ISBLANK(B2)=FALSE,LEN(J2)<=ReferenceTab!$G$11)	text	NO
I2:I7	Expression	=AND(ISBLANK(I2)=FALSE,LEN(I2)<=ReferenceTab!$G$10)	text	NO
H2:H7	Expression	=OR(H2="US",H2="OV",H2="BBUS",H2="BBOV",H2="BB2D")	text	NO
F2:F7	Expression	=ISNUMBER(F2)	text	NO
E2:E7	Expression	=AND(E2>=TODAY(),E2<=TODAY()+30)	text	NO
D2:D7	Expression	=AND(ISBLANK(B2)=FALSE,LEN(D2)<=ReferenceTab!$G$5)	text	NO
B2:B7	Expression	=AND(LEN(B2)<=ReferenceTab!$G$3)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2:G7	List	=AccountList
H2:H7	List	US,OV,BBUS,BBOV,BB2D