Insert row

[Cstolworthy]

I am new to VBA, but I am experienced in C#. So I am sure i am just having a disconnect somewhere with how to actually do this. I am working in Excel.

I have an array with some specific values in it. The array is declared as follows:

Dim CodesToSplit(2)
CodesToSplit(0) = 80000060
CodesToSplit(1) = 80000094

I need to go through all the rows and if the cell contents in Column G match any of the values in my array I need to do the following:
Insert a new row (above or below, it doesn't matter)
Copy the contents of the row to the new row
Take the value in column H and in one of the rows it should be 1, the other will be the original value of cell H - 1

So if the row is setup like this:

                         Cell G                        Cell H
Row 1               80000060                       4

After it should look like this:

                         Cell G                         Cell H
Row 1               80000060                       1
Row 2               80000060                       3

Any help would be greatly appreciated

[Leith Ross]

Hello Cstolworthy,

Welcome to the Forum!

Please post your workbook. With the workbook, we can then see the full layout, formulas, formatting, and any macro code the workbook contains. This saves time, and provides a way to test the code before the solution is provided, ensuring you get fast and accurate results.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[MarvinP]

H Cstolworthy and welcome to the forum.

Excel is great for exposing what it does and the vocabulary needed to do things. You need to "Record a Macro" doing the steps you describe above. Then look at what Excel recorded in the Macro Recorder. This will give you great insite to the VBA vocabulary and how it does stuff.

After seeing the vocabulary for inserting a row you are ready to build a loop to run down the rows looking for your constants. Put an IF .. Then statement in the loop and only insert a row if a constant is found.

To loop down rows use "Cells(RowCtr,ColCtr)" where RowCtr and ColCtr are numbers (longs).

Do a little debugging using Break Points in the VBA IDE (Visual Basic for Applications, Integrated Development Environment) and stepping through your code and you'll be a VBA expert in no time.

I hope this is a lot more helpful than just giving you THE answer. (Give a fish vs Teach to fish)

[Cstolworthy]

I have attached the workbook, one of the lines that I am looking to split specifically is row 43.

You can see that the CDM column matches, and the QTY_Units column has 2 in it. Because it is a quantity greater than 1, it needs splitting.

[MarvinP]

Hi again Cstolworthy,

Now with some data, I get an idea of what you are doing. I find text and values in Column G. This makes them sort funny and other bad stuff.

I've created a Pivot Table on a new sheet which may lead you to what you are ultimately after. I don't know what the other columns have to offer in your solution but do believe Pivot Tables may be where you are going. Try some of the power in Exce before trying to write (rewrite) code.

Find the attached with the start of a Pivot Table on the new sheet.