Excel 2007 : Offset based on criteria

[Rebel_42MDx]

Trying to learn VBA by first writing code to parse data. I am trying to shift cells to the right based on whether the row contains certain values.

In this example, I either want to shift all rows that start with 0 one cell to the right.

I have attached a "Before and After" spreadsheet showing raw data and desired result.

Any help greatly appreciated!

Thanks,

Reb

[JBeaucaire]

I'm not sure this qualifies as parsing, but just formatting.

If this is a large dataset, I would suggest creating a Range that incorporates all the cells that match your criteria, then move the whole range to the right all at once.

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

[Rebel_42MDx]

This works great--thank you! I read through the code and I think I understand the code except I'm uncertain about the "LR + 10".

Also, if I want to shift cells right with a value other than zero, what variables do I change?

For example, I tried to shift cells that begin with the letter O (not zero...), so I substituted O for 0 and I can't get the code to shift the letter O cells. What am I doing wrong here?

Thanks,

Reb

[JBeaucaire]

I keep the changes to the sheet to a minimum by building up a range of all the cells that fit the criteria. Inside the loop, I just want to keep adding to the already existing RNG, so I have to set the RNG outside the loop to start it off.

To accomplish that harmlessly, I find the last row with data, then make the first cell in the RNG a cell 10 rows outside the data.

Then I can use the simple UNION() command to keep expanding it with cells inside the data set.

To change from a number to a text string, put the string in quotes, like so:

Please Login or Register  to view this content.

[Rebel_42MDx]

I see--I was leaving out the double quotes. Makes sense since the letter "O" is text.

Thanks again for all of your help.

Reb