Trouble ending VBA subroutine

[Timmmmmm]

Hi, I am new-ish to VBA and trying to write an application to parse some data that comes out of an instrument I am using, and am having trouble understanding how to end a loop.

When this instrument outputs data, it concatenates individual data files for barcoded objects (plates) into a single data file, and I am developing an application to move blocks of data into separate worksheets and name the worksheets with the barcode number.

I am tryign to end my routine by defining "myLastRow" as the first row to contain a common output of the instrument "Basic assay information" and it is my impression that this should end the routine? However, it does not, and the app creates a new "output" sheet and commits a 1004 error when renaming the sheet since the block of data that it captures at the end of the file does not have an entry in the barcode cell (C3). Anyone have an idea of how to set this to end? I can't figure out how to turn it off, or set it to stop at "myLastRow - 1" ?

Thanks!

Tim

Sub PlateSeparatorParse()
 On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Output").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
ActiveSheet.Name = "Input"
Sheets.Add
ActiveSheet.Name = "Output"
   
Sheets("Input").Activate
mylastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
mylastcol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
For r = 1 To mylastrow
If Cells(r, 1).Value Like "Basic assay information*" Then
mylastrow = r
End If
Next r
cc = 1
rr = 1
For c = 1 To 24
If Not Cells(1, c).Value = "" Then

For r = 1 To mylastrow
If Not Cells(r, c).Value = "" Then
Range(Cells(r, c), (Cells(r + 20, c + 24))).Select
Selection.Copy
Sheets("Output").Select
Cells(1, 1).Select
Selection.PasteSpecial
''renames sheet to barcode
ActiveSheet.Name = [C3]

''adds new sheet "output"
Sheets.Add
ActiveSheet.Name = "Output"
cc = cc + 1
r = r + 20

End If
Sheets("Input").Select
Next r
rr = rr + 20
End If

cc = 1
Next c

MsgBox "Done"

End Sub

example data file

Plate information
Plate	Repeat	Barcode	Measured height	Chamber temperature at start	Chamber temperature at end	Humidity at start	Humidity at end	Ambient temperature at start	Ambient temperature at end
1	1	IAMBARCODE1	N/A	N/A	N/A	N/A	N/A	N/A	N/A
1	17	33	49	65	81	97	113	129	145	161	177	193	209	225	241	257	273	289	305	321	337	353	369
2	18	34	50	66	82	98	114	130	146	162	178	194	210	226	242	258	274	290	306	322	338	354	370
3	19	35	51	67	83	99	115	131	147	163	179	195	211	227	243	259	275	291	307	323	339	355	371
4	20	36	52	68	84	100	116	132	148	164	180	196	212	228	244	260	276	292	308	324	340	356	372
5	21	37	53	69	85	101	117	133	149	165	181	197	213	229	245	261	277	293	309	325	341	357	373
6	22	38	54	70	86	102	118	134	150	166	182	198	214	230	246	262	278	294	310	326	342	358	374
7	23	39	55	71	87	103	119	135	151	167	183	199	215	231	247	263	279	295	311	327	343	359	375
8	24	40	56	72	88	104	120	136	152	168	184	200	216	232	248	264	280	296	312	328	344	360	376
9	25	41	57	73	89	105	121	137	153	169	185	201	217	233	249	265	281	297	313	329	345	361	377
10	26	42	58	74	90	106	122	138	154	170	186	202	218	234	250	266	282	298	314	330	346	362	378
11	27	43	59	75	91	107	123	139	155	171	187	203	219	235	251	267	283	299	315	331	347	363	379
12	28	44	60	76	92	108	124	140	156	172	188	204	220	236	252	268	284	300	316	332	348	364	380
13	29	45	61	77	93	109	125	141	157	173	189	205	221	237	253	269	285	301	317	333	349	365	381
14	30	46	62	78	94	110	126	142	158	174	190	206	222	238	254	270	286	302	318	334	350	366	382
15	31	47	63	79	95	111	127	143	159	175	191	207	223	239	255	271	287	303	319	335	351	367	383
16	32	48	64	80	96	112	128	144	160	176	192	208	224	240	256	272	288	304	320	336	352	368	384
Plate information
Plate	Repeat	Barcode	Measured height	Chamber temperature at start	Chamber temperature at end	Humidity at start	Humidity at end	Ambient temperature at start	Ambient temperature at end
1	1	IAMBARCODE2	N/A	N/A	N/A	N/A	N/A	N/A	N/A
385	401	417	433	449	465	481	497	513	529	545	561	577	593	609	625	641	657	673	689	705	721	737	753
386	402	418	434	450	466	482	498	514	530	546	562	578	594	610	626	642	658	674	690	706	722	738	754
387	403	419	435	451	467	483	499	515	531	547	563	579	595	611	627	643	659	675	691	707	723	739	755
388	404	420	436	452	468	484	500	516	532	548	564	580	596	612	628	644	660	676	692	708	724	740	756
389	405	421	437	453	469	485	501	517	533	549	565	581	597	613	629	645	661	677	693	709	725	741	757
390	406	422	438	454	470	486	502	518	534	550	566	582	598	614	630	646	662	678	694	710	726	742	758
391	407	423	439	455	471	487	503	519	535	551	567	583	599	615	631	647	663	679	695	711	727	743	759
392	408	424	440	456	472	488	504	520	536	552	568	584	600	616	632	648	664	680	696	712	728	744	760
393	409	425	441	457	473	489	505	521	537	553	569	585	601	617	633	649	665	681	697	713	729	745	761
394	410	426	442	458	474	490	506	522	538	554	570	586	602	618	634	650	666	682	698	714	730	746	762
395	411	427	443	459	475	491	507	523	539	555	571	587	603	619	635	651	667	683	699	715	731	747	763
396	412	428	444	460	476	492	508	524	540	556	572	588	604	620	636	652	668	684	700	716	732	748	764
397	413	429	445	461	477	493	509	525	541	557	573	589	605	621	637	653	669	685	701	717	733	749	765
398	414	430	446	462	478	494	510	526	542	558	574	590	606	622	638	654	670	686	702	718	734	750	766
399	415	431	447	463	479	495	511	527	543	559	575	591	607	623	639	655	671	687	703	719	735	751	767
400	416	432	448	464	480	496	512	528	544	560	576	592	608	624	640	656	672	688	704	720	736	752	768
Plate information
Plate	Repeat	Barcode	Measured height	Chamber temperature at start	Chamber temperature at end	Humidity at start	Humidity at end	Ambient temperature at start	Ambient temperature at end
1	1	IAMBARCODE3	N/A	N/A	N/A	N/A	N/A	N/A	N/A
769	785	801	817	833	849	865	881	897	913	929	945	961	977	993	1009	1025	1041	1057	1073	1089	1105	1121	1137
770	786	802	818	834	850	866	882	898	914	930	946	962	978	994	1010	1026	1042	1058	1074	1090	1106	1122	1138
771	787	803	819	835	851	867	883	899	915	931	947	963	979	995	1011	1027	1043	1059	1075	1091	1107	1123	1139
772	788	804	820	836	852	868	884	900	916	932	948	964	980	996	1012	1028	1044	1060	1076	1092	1108	1124	1140
773	789	805	821	837	853	869	885	901	917	933	949	965	981	997	1013	1029	1045	1061	1077	1093	1109	1125	1141
774	790	806	822	838	854	870	886	902	918	934	950	966	982	998	1014	1030	1046	1062	1078	1094	1110	1126	1142
775	791	807	823	839	855	871	887	903	919	935	951	967	983	999	1015	1031	1047	1063	1079	1095	1111	1127	1143
776	792	808	824	840	856	872	888	904	920	936	952	968	984	1000	1016	1032	1048	1064	1080	1096	1112	1128	1144
777	793	809	825	841	857	873	889	905	921	937	953	969	985	1001	1017	1033	1049	1065	1081	1097	1113	1129	1145
778	794	810	826	842	858	874	890	906	922	938	954	970	986	1002	1018	1034	1050	1066	1082	1098	1114	1130	1146
779	795	811	827	843	859	875	891	907	923	939	955	971	987	1003	1019	1035	1051	1067	1083	1099	1115	1131	1147
780	796	812	828	844	860	876	892	908	924	940	956	972	988	1004	1020	1036	1052	1068	1084	1100	1116	1132	1148
781	797	813	829	845	861	877	893	909	925	941	957	973	989	1005	1021	1037	1053	1069	1085	1101	1117	1133	1149
782	798	814	830	846	862	878	894	910	926	942	958	974	990	1006	1022	1038	1054	1070	1086	1102	1118	1134	1150
783	799	815	831	847	863	879	895	911	927	943	959	975	991	1007	1023	1039	1055	1071	1087	1103	1119	1135	1151
784	800	816	832	848	864	880	896	912	928	944	960	976	992	1008	1024	1040	1056	1072	1088	1104	1120	1136	1152
Basic assay information
Assay ID:				1313
Assay Started:			########
Assay Finished:			########
Assay Exported:			########
Protocol ID:			9999
Protocol Name:			TEST1

<tbody>
</tbody>

 

[Timmmmmm]

SOLVED.

There was a gap between my "range" blocks that I am moving and mylastrow.

executing the loop from as
r= r to mylastrow - 1 solved the issue.