Help me automate a procedure in excel

Question

I have an excel sheet with values in columns A, B and C like this:

      A      B      C
1    8.22   1.99902 32.48974
2    8.22   3.04698 33.92426
3    8.22   2.26374 33.1547
4    8.22   2.78227 33.2593
6    8.22   2.46798 33.34269
6    8.22   2.57802 33.67131
7    8.22   2.46798 32.7427
8    8.22   2.57802 33.07131

There is also a cell (say for example F1) containing a value that can only be 1,2 or 3.

I would like to create a commandbutton to perform the following job: For each pair of lines (which cosnist a group) insert 2 new lines below them, and populate them with values like this (suppose we are dealing with the group residing in lines 9 and 10)

If F1 = 1 THEN
A11 = A10   B11 = B10   C11 = C9
A12 = A10   B12 = B9    C12 = C10

If F1 = 2 THEN
A11 = A10   B11 = B10   C11 = C9
A12 = A9   B12 = B10    C12 = C10

If F1 = 3 THEN
A11 = A10   B11 = B9   C11 = C10
A12 = A9   B12 = B10    C12 = C10

Finaly set the background color for the newely inserted lines to yellow.

Could you please help me accomplish this task?

P.S. Once a button like this was pressed, what would you recommend for an Undo capability?

Answer 1

A few things:

• You should try to create Macros and then see what kind of code they produce. This will help you see what VBA code is needed to manipulate the Excel sheet.
• After you create a few macros, learn how to do loops so that you can run the process multiple times for all of the cells that you need.
• You can't undo the results of a macro. You might try to write a separate function to undo the results of a previous function, but sounds like a ton of work. I would recommend creating a copy of the worksheet you have and working with a new sheet for your development phase.

This should get you started. There are plenty of people on SO that could figure this out for you, but perhaps this would be a good problem to step into a little bit at a time; then ask questions when you stock on a specific detail.

For example, if you see code generated by the macro that looks like ActiveSheet.Range("C2").Select, you might ask, "I see how to select a single cell. How do I select an entire Row?" That will get you more targeted answers to help you solve your problem.

Answer 2

Supposing that your data start at Row 1 and the commandbutton's name is CommandButton1, try adding the following code in CommandButton1 click event. I used a bottom-up approach because it was easier for me to handle row numbers while looping.

Private Sub CommandButton1_Click()
    Dim lines_count As Integer
    Dim fixed_column As Integer
    Dim i As Integer
    lines_count = Application.WorksheetFunction.Count(Range("A:A"))
    fixed_column = Range("F1").Value

    For i = lines_count + 2 To 4 Step -2 
        Rows(i - 1 & ":" & i).Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        Select Case fixed_column
        Case 1
            Cells(i - 1, 1).Value = Cells(i - 2, 1).Value
            Cells(i, 1).Value = Cells(i - 2, 1).Value
            Cells(i - 1, 2).Value = Cells(i - 2, 2).Value
            Cells(i, 2).Value = Cells(i - 3, 2).Value
            Cells(i - 1, 3).Value = Cells(i - 3, 3).Value
            Cells(i, 3).Value = Cells(i - 2, 3).Value
        Case 2
            Cells(i - 1, 1).Value = Cells(i - 2, 1).Value
            Cells(i, 1).Value = Cells(i - 3, 1).Value
            Cells(i - 1, 2).Value = Cells(i - 2, 2).Value
            Cells(i, 2).Value = Cells(i - 2, 2).Value
            Cells(i - 1, 3).Value = Cells(i - 3, 3).Value
            Cells(i, 3).Value = Cells(i - 2, 3).Value
        Case 3
            Cells(i - 1, 1).Value = Cells(i - 2, 1).Value
            Cells(i, 1).Value = Cells(i - 3, 1).Value
            Cells(i - 1, 2).Value = Cells(i - 3, 2).Value
            Cells(i, 2).Value = Cells(i - 2, 2).Value
            Cells(i - 1, 3).Value = Cells(i - 2, 3).Value
            Cells(i, 3).Value = Cells(i - 2, 3).Value
        End Select
        Range("A" & CStr(i - 1) & ":C" & CStr(i)).Select
        Selection.Interior.ColorIndex = 6

    Next
End Sub