LearnVBA83
Board Regular
- Joined
- Dec 1, 2016
- Messages
- 109
- Office Version
- 365
- Platform
- Windows
Hi VBA SuperUsers,
I've converted a txt file to excel and as many of you know the report headers are repeated in my excel document based on each change in page number on the original txt file. What I would like to do is take one piece of information from the header (the bank lockbox) and paste it beside each transaction on each page in excel. The trick is the lock box numbers do change within the title and there is no consistency. So is this possible with a macro. It would basically be saying copy Lockbox number and paste by each transaction until change in page then copy and paste the next lockbox number by each transaction.... etc. Once I figure out how to get the lockbox from the title beside each transaction, I will have the macro delete all of the report headings to give me a more manageable data set. Hopefully this will make since. It's hard to explain without being able to attach an example. For the time being below is a paste of what I have. This is only the first 3 pages out of a bunch of pages in this excel file. The bold lockboxes in column F are examples of what I want to paste beside each transaction. As you can see it changes in F57. Column K is not in the report but is an example of what I would like for the macro to do. Any help would be greatly appreciated. I've worked on this half the day and i'm getting nowhere. Thanks so much for anyone willing to help
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
I've converted a txt file to excel and as many of you know the report headers are repeated in my excel document based on each change in page number on the original txt file. What I would like to do is take one piece of information from the header (the bank lockbox) and paste it beside each transaction on each page in excel. The trick is the lock box numbers do change within the title and there is no consistency. So is this possible with a macro. It would basically be saying copy Lockbox number and paste by each transaction until change in page then copy and paste the next lockbox number by each transaction.... etc. Once I figure out how to get the lockbox from the title beside each transaction, I will have the macro delete all of the report headings to give me a more manageable data set. Hopefully this will make since. It's hard to explain without being able to attach an example. For the time being below is a paste of what I have. This is only the first 3 pages out of a bunch of pages in this excel file. The bold lockboxes in column F are examples of what I want to paste beside each transaction. As you can see it changes in F57. Column K is not in the report but is an example of what I would like for the macro to do. Any help would be greatly appreciated. I've worked on this half the day and i'm getting nowhere. Thanks so much for anyone willing to help
A | B | C | D | E | F | G | H | I | J | K | |
1 | RUN DATE: 0 | 01/01/2017 | CompanyName-EBS | PAGE | 1 | ||||||
2 | DEPOSIT DATE | : 01/01/17 | L | OCKBOX REPORT - B | OX 0213 | OX 0213 | |||||
3 | FRO | M BankName, N.A. | 21000089 | OX 0213 | |||||||
4 | OX 0213 | ||||||||||
5 | CHK NBR CH | K ACCT NBR | CHK ABA # | CHECK AMT | INVOICE AMT | INVOICE | # CUST # | BATCH | SEQ# | SYS SEQ # | OX 0213 |
6 | OX 0213 | ||||||||||
7 | 2659 | 1361297 | 322273379 | $2,070.25 | 1 | 1 | 10001 | OX 0213 | |||
8 | $2,070.25 | H4105631 | 8.96393E+11 | 1 | 2 | 10001 | OX 0213 | ||||
9 | 3272 | 898478011 | 322271627 | $5,279.02 | 1 | 3 | 10002 | OX 0213 | |||
10 | $5,279.02 | H4083305 | 8.18271E+11 | 1 | 4 | 10002 | OX 0213 | ||||
11 | 3489 | 3244286245 | 122000247 | $1,329.36 | 1 | 5 | 10003 | OX 0213 | |||
12 | $1,329.36 | H4122275 | 9.29505E+11 | 1 | 6 | 10003 | OX 0213 | ||||
13 | 23979 | 1064174530 | 122037760 | $2,765.42 | 1 | 7 | 10004 | OX 0213 | |||
14 | $2,765.42 | H4131700 | 9.43587E+11 | 1 | 8 | 10004 | OX 0213 | ||||
15 | 8375 | 202001461 | 121144311 | $634.06 | 1 | 9 | 10005 | OX 0213 | |||
16 | $634.06 | H4062609 | 2281871002 | 1 | 10 | 10005 | OX 0213 | ||||
17 | 7861 | 8915058831 | 122000247 | $1,658.94 | 1 | 11 | 10006 | OX 0213 | |||
18 | $1,658.94 | H4127782 | 9.36098E+11 | 1 | 12 | 10006 | OX 0213 | ||||
19 | 5596 | 206489841 | 322271724 | $4,472.57 | 1 | 13 | 10007 | OX 0213 | |||
20 | $4,472.57 | H3892726 | 8.9737E+11 | 1 | 14 | 10007 | OX 0213 | ||||
21 | 11460 | 20102745 | 122243884 | $4,575.43 | 1 | 15 | 10008 | OX 0213 | |||
22 | $4,575.43 | H4132901 | 9.44716E+11 | 1 | 16 | 10008 | OX 0213 | ||||
23 | 7401 | 729009513 | 122000661 | $4,253.57 | 1 | 17 | 10009 | OX 0213 | |||
24 | $4,253.57 | H4077731 | 8.00785E+11 | 1 | 18 | 10009 | OX 0213 | ||||
25 | 4099 | 3890785494 | 322271627 | $1,095.27 | 1 | 19 | 10010 | OX 0213 | |||
26 | $1,095.27 | H4092838 | 8.46579E+11 | 1 | 20 | 10010 | OX 0213 | ||||
27 | 9888 | 1790201431 | 122000661 | $1,039.22 | 1 | 21 | 10011 | OX 0213 | |||
28 | $1,039.22 | H4118157 | 9.20678E+11 | 1 | 22 | 10011 | OX 0213 | ||||
29 | 21420 | 102549125 | 43400036 | $11,051.26 | 1 | 23 | 10012 | OX 0213 | |||
30 | $11,051.26 | H4122172 | 9.29321E+11 | 1 | 24 | 10012 | OX 0213 | ||||
31 | 3491 | 1048571536 | 43000096 | $6,476.40 | 1 | 25 | 10013 | OX 0213 | |||
32 | $6,476.40 | H4104745 | 8.9409E+11 | 1 | 26 | 10013 | OX 0213 | ||||
33 | 9036 | 1515196 | 43403224 | $7,066.06 | 1 | 27 | 10014 | OX 0213 | |||
34 | $7,066.06 | H4126046 | 9.34363E+11 | 1 | 28 | 10014 | OX 0213 | ||||
35 | 17397 | 1416018412 | 243374218 | $27,891.00 | 1 | 29 | 10015 | OX 0213 | |||
36 | $27,891.00 | H4120807 | 9.2538E+11 | 1 | 30 | 10015 | OX 0213 | ||||
37 | 48713 | 1059742101 | 41215032 | $5,442.97 | 1 | 31 | 10016 | OX 0213 | |||
38 | $5,442.97 | H4118888 | 9.21783E+11 | 1 | 32 | 10016 | OX 0213 | ||||
39 | 4400 | 104011837 | 243374234 | $1,831.24 | 1 | 33 | 10017 | OX 0213 | |||
40 | $1,831.24 | H4124698 | 9.33053E+11 | 1 | 34 | 10017 | OX 0213 | ||||
41 | 12580 | 130600620 | 43400036 | $3,738.58 | 1 | 35 | 10018 | OX 0213 | |||
42 | $3,738.58 | H4124353 | 9.32755E+11 | 1 | 36 | 10018 | OX 0213 | ||||
43 | 9541 | 205009574 | 31100102 | $6,899.02 | 1 | 37 | 10019 | OX 0213 | |||
44 | $6,899.02 | H4085675 | 8.24415E+11 | 1 | 38 | 10019 | OX 0213 | ||||
45 | 26540 | 1.39E+12 | 31309123 | $9,117.60 | 1 | 39 | 10020 | OX 0213 | |||
46 | $9,117.60 | H4087670 | 8.30057E+11 | 1 | 40 | 10020 | OX 0213 | ||||
47 | 8233 | 210261079 | 31100102 | $17,902.10 | 1 | 41 | 10021 | OX 0213 | |||
48 | $17,902.10 | H4125474 | 9.34164E+11 | 1 | 42 | 10021 | OX 0213 | ||||
49 | 10037 | 290548801 | 31301422 | $3,733.45 | 1 | 43 | 10022 | OX 0213 | |||
50 | $3,733.45 | H4130919 | 9.4291E+11 | 1 | 44 | 10022 | OX 0213 | ||||
51 | 3763 | 2524179175 | 122105744 | $166.80 | 1 | 45 | 10023 | OX 0213 | |||
52 | $166.80 | H4088456 | 8.33813E+11 | 1 | 46 | 10023 | OX 0213 | ||||
53 | 59690 | 12534405 | 122100024 | $2,458.87 | 1 | 47 | 10024 | OX 0213 | |||
54 | $2,458.87 | H4058725 | 716111001 | 1 | 48 | 10024 | OX 0213 | ||||
55 | 9129 | 2502039213 | 122105744 | $2,219.49 | 1 | 49 | 10025 | OX 0213 | |||
56 | RUN DATE: 0 | 01/01/2017 | CompanyName-EBS | PAGE | 2 | OX 0213 | |||||
57 | DEPOSIT DATE | : 01/01/17 | L | OCKBOX REPORT - B | OX 4777 | OX 4777 | |||||
58 | FRO | M BankName, N.A. | 21000089 | OX 4777 | |||||||
59 | OX 4777 | ||||||||||
60 | CHK NBR CH | K ACCT NBR | CHK ABA # | CHECK AMT | INVOICE AMT | INVOICE | # CUST # | BATCH | SEQ# | SYS SEQ # | OX 4777 |
61 | OX 4777 | ||||||||||
62 | $2,219.49 | H4081107 | 8.12324E+11 | 1 | 50 | 10025 | OX 4777 | ||||
63 | 14095 | 6521800043 | 101000695 | $2,950.45 | 1 | 51 | 10026 | OX 4777 | |||
64 | $2,950.45 | H4085573 | 8.24301E+11 | 1 | 52 | 10026 | OX 4777 | ||||
65 | 78141 | 653245043 | 122100024 | $16,272.20 | 1 | 53 | 10027 | OX 4777 | |||
66 | $16,272.20 | H4073349 | 8.00255E+11 | 1 | 54 | 10027 | OX 4777 | ||||
67 | 114 | 869196589 | 122100024 | $3,541.32 | 1 | 55 | 10028 | OX 4777 | |||
68 | $3,541.32 | H3950057 | 9.49047E+11 | 1 | 56 | 10028 | OX 4777 | ||||
69 | 20986 | 5282803120 | 122105278 | $1,874.40 | 1 | 57 | 10029 | OX 4777 | |||
70 | $1,874.40 | H4111620 | 9.08655E+11 | 1 | 58 | 10029 | OX 4777 | ||||
71 | 17565 | 7.0103E+11 | 322172496 | $776.42 | 1 | 59 | 10030 | OX 4777 | |||
72 | $776.42 | H4145383 | 9.64787E+11 | 1 | 60 | 10030 | OX 4777 | ||||
73 | 41513 | 739928872 | 122100024 | $6,793.60 | 1 | 61 | 10031 | OX 4777 | |||
74 | $6,793.60 | H4082058 | 8.15243E+11 | 1 | 62 | 10031 | OX 4777 | ||||
75 | 11647 | 6706568539 | 122105278 | $7,823.00 | 1 | 63 | 10032 | OX 4777 | |||
76 | $7,823.00 | H4079864 | 8.0802E+11 | 1 | 64 | 10032 | OX 4777 | ||||
77 | 8156 | 2527036162 | 122105744 | $6,543.06 | 1 | 65 | 10033 | OX 4777 | |||
78 | $6,543.06 | H4077322 | 8.00747E+11 | 1 | 66 | 10033 | OX 4777 | ||||
79 | 4695 | 2296801331 | 122105278 | $4,661.00 | 1 | 67 | 10034 | OX 4777 | |||
80 | $4,661.00 | H4103301 | 8.89121E+11 | 1 | 68 | 10034 | OX 4777 | ||||
81 | 8050 | 93466625 | 63100277 | $3,781.00 | 1 | 69 | 10035 | OX 4777 | |||
82 | $3,781.00 | H4061057 | 1645161001 | 1 | 70 | 10035 | OX 4777 | ||||
83 | 1592 | 2.00004E+12 | 62000080 | $1,206.77 | 1 | 71 | 10036 | OX 4777 | |||
84 | $1,206.77 | H3867060 | 9.13982E+11 | 1 | 72 | 10036 | OX 4777 | ||||
85 | 1593 | 2.00004E+12 | 62000080 | $1,206.77 | 1 | 73 | 10037 | OX 4777 | |||
86 | $1,206.77 | H4054966 | 9.13982E+11 | 1 | 74 | 10037 | OX 4777 | ||||
87 | 4844 | 9062640848 | 67092022 | $2,733.00 | 1 | 75 | 10038 | OX 4777 | |||
88 | $2,733.00 | H4066046 | 94236051001 | 1 | 76 | 10038 | OX 4777 | ||||
89 | 10931 | 3774324336 | 63100277 | $1,535.31 | 1 | 77 | 10039 | OX 4777 | |||
90 | $1,535.31 | H4057456 | 194151001 | 1 | 78 | 10039 | OX 4777 | ||||
91 | 63697 | 17000030333 | 63102152 | $1,062.41 | 1 | 79 | 10040 | OX 4777 | |||
92 | $1,062.41 | H4060462 | 1420131002 | 1 | 80 | 10040 | OX 4777 | ||||
93 | 2247 | 558155110 | 267084131 | $1,132.44 | 1 | 81 | 10041 | OX 4777 | |||
94 | $1,132.44 | H4059838 | 1198611001 | 1 | 82 | 10041 | OX 4777 | ||||
95 | 27862 | 252590440 | 122101706 | $4,238.06 | 1 | 83 | 10042 | OX 4777 | |||
96 | $4,238.06 | H3933255 | 9.23671E+11 | 1 | 84 | 10042 | OX 4777 | ||||
97 | 27737 | 9686590218 | 122105278 | $13,509.00 | 1 | 85 | 10043 | OX 4777 | |||
98 | $13,509.00 | H4106720 | 9.00024E+11 | 1 | 86 | 10043 | OX 4777 | ||||
99 | 7707 | 2517289518 | 122105744 | $922.98 | 1 | 87 | 10044 | OX 4777 | |||
100 | $922.98 | H4140118 | 9.54561E+11 | 1 | 88 | 10044 | OX 4777 | ||||
101 | 3248 | 904156718 | 122100024 | $17.20 | 1 | 89 | 10045 | OX 4777 | |||
102 | $17.20 | H4110327 | 9.06303E+11 | 1 | 90 | 10045 | OX 4777 | ||||
103 | 25351 | 1281588 | 122100024 | $5,870.90 | 1 | 91 | 10046 | OX 4777 | |||
104 | $5,870.90 | H4141023 | 9.56013E+11 | 1 | 92 | 10046 | OX 4777 | ||||
105 | 2612 | 79152198 | 63104668 | $3,904.72 | 1 | 93 | 10047 | OX 4777 | |||
106 | $3,904.72 | H4097472 | 8.64709E+11 | 1 | 94 | 10047 | OX 4777 | ||||
107 | 34231 | 6855504191 | 31201328 | $264.60 | 1 | 95 | 10048 | OX 4777 | |||
108 | $264.60 | H4062632 | 2293531001 | 1 | 96 | 10048 | OX 4777 | ||||
109 | 10327 | 8022867437 | 31207607 | $2,848.50 | 1 | 97 | 10049 | OX 4777 | |||
110 | $2,848.50 | H4112134 | 9.10087E+11 | 1 | 98 | 10049 | OX 4777 | ||||
111 | RUN DATE: 0 | 01/01/2017 | CompanyName-EBS | PAGE | 3 | OX 4777 | |||||
112 | DEPOSIT DATE | : 01/01/17 | L | OCKBOX REPORT - B | OX 5128 | OX 5128 | |||||
113 | FRO | M BankName, N.A. | 21000089 | OX 5128 | |||||||
114 | OX 5128 | ||||||||||
115 | CHK NBR CH | K ACCT NBR | CHK ABA # | CHECK AMT | INVOICE AMT | INVOICE | # CUST # | BATCH | SEQ# | SYS SEQ # | OX 5128 |
116 | OX 5128 | ||||||||||
117 | 39229 | 1112522 | 231372691 | $1,832.37 | 1 | 99 | 10050 | OX 5128 | |||
118 | $1,832.37 | H4070118 | 1.30061E+11 | 1 | 100 | 10050 | OX 5128 | ||||
119 | 23098 | 2291822163 | 63100277 | $2,820.00 | 1 | 101 | 10051 | OX 5128 | |||
120 | $2,820.00 | H4066225 | 1.00374E+11 | 1 | 102 | 10051 | OX 5128 | ||||
121 | 6120 | 2.00004E+12 | 63107513 | $1,266.00 | 1 | 103 | 10052 | OX 5128 | |||
122 | $1,266.00 | H4050331 | 85311041001 | 1 | 104 | 10052 | OX 5128 | ||||
123 | 11815 | 3024075 | 11202392 | $2,131.12 | 1 | 105 | 10053 | OX 5128 | |||
124 | $2,131.12 | H4096923 | 8.63001E+11 | 1 | 106 | 10053 | OX 5128 | ||||
125 | 9944 | 104413398 | 11200608 | $1,227.00 | 1 | 107 | 10054 | OX 5128 | |||
126 | $1,227.00 | H4061239 | 1703251001 | 1 | 108 | 10054 | OX 5128 | ||||
127 | 9003 | 96192614 | 11200608 | $9,770.19 | 1 | 109 | 10055 | OX 5128 | |||
128 | $9,770.19 | H4107045 | 9.00602E+11 | 1 | 110 | 10055 | OX 5128 | ||||
129 | 3527 | 9803634224 | 221172186 | $3,223.44 | 1 | 111 | 10056 | OX 5128 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>