Dear experts of VBA and excel,
I'm a beginner to VBA programming and new to MrExcel. I have searched the internet and MrExcel far and wide but I can't find the solution to my problem. I have data that is not very nice arranged for analysis as it comes from a word file. The original file has 3 column and 2515 rows.
What I would like to find the next non-blank in column c, in the example its 51. When non-blank is found take the data from Column A and one row lower than the non-blank and copy it to a new sheet under column MEP. In this case the example it's "Jessica Stegrud".
The next action is similar. Again find the first non-blank in Column C which is 51. Than return the value from Column a but now 3 rows lower: "Recital 1 a (new)" to the new sheet under Column: "reference"
Same action again but copy from Column A 5 rows lower the "a" to the other sheet under column "original"
Last action is the same but not from Column B 5 rows lower the "(1 a) However, a transition to e-mobility with the purpose to reduce the emissions of carbon dioxide requires fossil-free production of electricity." to other sheet under Column: "change"
The last action is which is to find the word "justification" in Column A and past the content from the 2 rows below to the other sheet under justification. In this case it's: "Putting a value on lithium batteries and accumulators increases the interest and willingness of end users to correctly dispose batteries, which is especially important for critical material flows.
I hope I made my self clear and I hope someone can help me in the right direction or links to my intention rearrangement.
Below is the original:
and the intended changes:
I'm a beginner to VBA programming and new to MrExcel. I have searched the internet and MrExcel far and wide but I can't find the solution to my problem. I have data that is not very nice arranged for analysis as it comes from a word file. The original file has 3 column and 2515 rows.
What I would like to find the next non-blank in column c, in the example its 51. When non-blank is found take the data from Column A and one row lower than the non-blank and copy it to a new sheet under column MEP. In this case the example it's "Jessica Stegrud".
The next action is similar. Again find the first non-blank in Column C which is 51. Than return the value from Column a but now 3 rows lower: "Recital 1 a (new)" to the new sheet under Column: "reference"
Same action again but copy from Column A 5 rows lower the "a" to the other sheet under column "original"
Last action is the same but not from Column B 5 rows lower the "(1 a) However, a transition to e-mobility with the purpose to reduce the emissions of carbon dioxide requires fossil-free production of electricity." to other sheet under Column: "change"
The last action is which is to find the word "justification" in Column A and past the content from the 2 rows below to the other sheet under justification. In this case it's: "Putting a value on lithium batteries and accumulators increases the interest and willingness of end users to correctly dispose batteries, which is especially important for critical material flows.
I hope I made my self clear and I hope someone can help me in the right direction or links to my intention rearrangement.
Below is the original:
| Book4 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Amendment | |||
| 2 | Jessica Stegrud | |||
| 3 | Proposal for a regulation | |||
| 4 | Recital 1 a (new) | |||
| 5 | Text proposed by the Commission | Amendment | ||
| 6 | (1 a) However, a transition to e-mobility with the purpose to reduce the emissions of carbon dioxide requires fossil-free production of electricity. | |||
| 7 | Or{EN}en</Original> | |||
| 8 | Amendment | |||
| 9 | Jessica Stegrud | |||
| 10 | Proposal for a regulation | |||
| 11 | Recital 2 | |||
| 12 | Text proposed by the Commission | Amendment | ||
| 13 | (2) Batteries are thus an important source of energy and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality. | (2) Batteries are thus an important energy carrier and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality. | ||
| 14 | Or{EN}en</Original> | |||
| 15 | Amendment | |||
| 16 | Hildegard Bentele | |||
| 17 | Proposal for a regulation | |||
| 18 | Recital 27 | |||
| 19 | Text proposed by the Commission | Amendment | ||
| 20 | . Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. | Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. Furthermore, given the steadily increasing energy density in lithium-based batteries and accumulators, fire incidents endangering humans and the environment have been increasing and hampering the circular economy. To ensure safe end-of-life treatment and avoid dangerous disposal, a European deposit refund scheme shall be put in place one year after the entry into force of this Regulation. Assigning a value to batteries and waste batteries is required to avoid risks that result from incorrect disposal, in particular fire incidents endangering humans and the environment. | ||
| 21 | Or{EN}en</Original> | |||
| 22 | Justification | |||
| 23 | Putting a value on lithium batteries and accumulators increases the interest and willingness of end users to correctly dispose batteries, which is especially important for critical material flows. | |||
Sheet1 | ||||
and the intended changes:
| Book4 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | amendment | MEPs | reference | original | change | justification | ||
| 2 | 51 | Jessica Stegrud | Recital 1 a (new) | (1 a) However, a transition to e-mobility with the purpose to reduce the emissions of carbon dioxide requires fossil-free production of electricity. | ||||
| 3 | 52 | Jessica Stegrud | Recital 2 | (2) Batteries are thus an important source of energy and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality. | (2) Batteries are thus an important energy carrier and one of the key enablers for sustainable development, green mobility, clean energy and climate neutrality. | |||
| 4 | 60 | Hildegard Bentele | Recital 27 | . Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. | Parameters to be considered in safety tests should therefore be laid down for those energy storage systems. Furthermore, given the steadily increasing energy density in lithium-based batteries and accumulators, fire incidents endangering humans and the environment have been increasing and hampering the circular economy. To ensure safe end-of-life treatment and avoid dangerous disposal, a European deposit refund scheme shall be put in place one year after the entry into force of this Regulation. Assigning a value to batteries and waste batteries is required to avoid risks that result from incorrect disposal, in particular fire incidents endangering humans and the environment. | Putting a value on lithium batteries and accumulators increases the interest and willingness of end users to correctly dispose batteries, which is especially important for critical material flows. | ||
Sheet2 | ||||||||
