Issue with using multiple cell refs in a macro

[andrewb90]

here is the code I am currently using:

Sub shift_1()
    Sheets("Input").Select
    If Range("B2") = "1" Then '**********************1****************
        Sheets("B1").Select
        Columns("G:G").Select
        Selection.Insert Shift:=xlToRight
        Range("G1").Select 'TOTAL ACTIONS
        ActiveCell.FormulaR1C1 = "1"
    End If
    If Range("B2") = "2" Then '**********************2****************
        Sheets("B1").Select
        Columns("G:G").Select
        Selection.Insert Shift:=xlToRight
        Range("G1").Select 'TOTAL ACTIONS
        ActiveCell.FormulaR1C1 = "1"
        Range("G3").Select 'add on sales
        ActiveCell.FormulaR1C1 = "1"
    End If
    If Range("B2") = "3" Then '**********************3****************
        Sheets("B1").Select
        Columns("G:G").Select
        Selection.Insert Shift:=xlToRight
        Range("G1").Select 'TOTAL ACTIONS
        ActiveCell.FormulaR1C1 = "1"
        Range("G5").Select 'soda sales
        ActiveCell.FormulaR1C1 = "1"
    End If
    If Range("B2") = "4" Then '**********************4****************
        Sheets("B1").Select
        Columns("G:G").Select
        Selection.Insert Shift:=xlToRight
        Sheets("B1").Select
        Columns("G:G").Select
        Selection.Insert Shift:=xlToRight
        Range("G8").Select 'pizza sales
        ActiveCell.FormulaR1C1 = "1"
    End If

What I am trying to do is in each section add another variable without making the code extremely long. Basically after B2 has a number, I want to check if B11:J11 has the number 1 in it. If it does, I want all the same code that is going to run in sheet 'B1' to also run in sheet G1 or G2, G3,etc. depending on which cell has the number 1 in it (9 possibilities)

I am hoping somebody will have a more efficient code that what I can think of, because I don't what to do 9 If blocks in every section of this code. (the numbers range from 1-50)

I hope this makes some sense...

Thanks,

Andrew

 

[JLGWhiz]

You mighth save a little bit of work if you use the Select Case Method.
Also use direct commands instead of the Select and Activate. Here is an example of the Select case method:

Code:
Sub shift_1()
With Sheets("Input")
Select Case .Range("B2").Value
Case Is = "1"
With Sheets("B1")
.Columns(7).Insert
.Range("G1") = "1"
End With
Case Is = "2"
With Sheets("B1")
.Columns(7).Insert
.Range("G1") = "1"
.Range("G3") = "1"
End With

'etc....
End Select
End Sub
Code:

Since each value of cell B2 has a requires a different posting combination, and the combinations are not sequentially incremented, i don't see a way to make this into a loop. If you are uncomfortable with the Select Case method, you might want to look at the If...ElseIf...Then method. It would save you a few lines of typing for fifty different conditions.