Trouble ending VBA subroutine

Timmmmmm

New Member
Joined
Aug 8, 2014
Messages
2
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
Code:
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>
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Timmmmmm

New Member
Joined
Aug 8, 2014
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,354
Messages
5,624,183
Members
416,017
Latest member
moritz210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top