Insert columns and values programmatically with IF statement

  • Thread starter Thread starter kittronald
  • Start date Start date
K

kittronald

I'm trying to perform the following with the least amount of code.

1) Test the value of "x" and if TRUE, do the following ...

2) On Sheet1, select column B and insert 4 columns to the right

3) On Sheet1, enter the following text values:

B1 = "SUM"
C1 = "PRODUCT"
D1 = "QUOTIENT"
E1 = "SUMPRODUCT"

4) On Sheet1, enter the following formulas:

B2 =SUM(1,1)
C2 =PRODUCT(1,1)
D2 =QUOTIENT(1,1)
E2 =SUMPRODUCT(1,1)

With Sheet1
If x = "SUM" Then
Columns($B:$B).Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "SUM"
Range("C1").Select
ActiveCell.FormulaR1C1 = "PRODUCT"
Range("C1").Select
ActiveCell.FormulaR1C1 = "QUOTIENT"
Range("D1").Select
ActiveCell.FormulaR1C1 = "SUMPRODUCT"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SUM(1,1)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=PRODUCT(1,1)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=QUOTIENT(1,1)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(1,1)"
Else
End With


Is there any way to do this with less code ?



- Ronald K.
 
    I'm trying to perform the following with the least amount of code..

    1) Test the value of "x" and if TRUE, do the following ...

    2) On Sheet1, select column B and insert 4 columns to the right

    3) On Sheet1, enter the following text values:

            B1 = "SUM"
            C1 = "PRODUCT"
            D1 = "QUOTIENT"
            E1 = "SUMPRODUCT"

    4) On Sheet1, enter the following formulas:

            B2    =SUM(1,1)
            C2    =PRODUCT(1,1)
            D2    =QUOTIENT(1,1)
            E2    =SUMPRODUCT(1,1)

    With Sheet1
        If x = "SUM" Then
            Columns($B:$B).Select
            Selection.Insert Shift:=xlToRight
            Selection.Insert Shift:=xlToRight
            Selection.Insert Shift:=xlToRight
            Selection.Insert Shift:=xlToRight
            Range("B1").Select
            ActiveCell.FormulaR1C1 = "SUM"
            Range("C1").Select
            ActiveCell.FormulaR1C1 = "PRODUCT"
            Range("C1").Select
            ActiveCell.FormulaR1C1 = "QUOTIENT"
            Range("D1").Select
            ActiveCell.FormulaR1C1 = "SUMPRODUCT"
            Range("B2").Select
            ActiveCell.FormulaR1C1 = "=SUM(1,1)"
            Range("C2").Select
            ActiveCell.FormulaR1C1 = "=PRODUCT(1,1)"
            Range("D2").Select
            ActiveCell.FormulaR1C1 = "=QUOTIENT(1,1)"
            Range("E2").Select
            ActiveCell.FormulaR1C1 = "=SUMPRODUCT(1,1)"
        Else
    End With

    Is there any way to do this with less code ?

- Ronald K.
Click to expand...

Yes, It could be greatly simplified.
Send your file with a complete explanation and before/after examples
to dguillett1 @gmail.com
 
Don,

Thanks for the quick response.

I generally like to keep solutions in newsgroup postings so others can
benefit.



- Ronald K.
 
Don,

    Thanks for the quick response.

    I generally like to keep solutions in newsgroup postings so others can
benefit.

- Ronald K.
Click to expand...

I don't understand your requirement and don't feel inclined to
recreate a file to test. I always post my solutions back to any ng.
So................
 
Try...

Sub InsertCols()
Dim vHdrs As Variant
Const sHeaders As String = "SUM,PRODUCT,QUOTIENT,SUMPRODUCT"
Const sFormulas As String = _
"=SUM(1,1):=PRODUCT(1,1):=QUOTIENT(1,1):=SUMPRODUCT(1,1)"
vHdrs = Split(sHeaders, ",")
Const x As String = "SUM" '//for testing only
With Sheet1
If x = vHdrs(0) Then
.Columns("C:F").Insert
.Range("B1").Resize(, 4) = vHdrs
.Range("B2").Resize(, 4).Formula = Split(sFormulas, ":")
End If 'x = vHdrs(0)
End With 'Sheet1
End Sub
 
Maybe a better approach...

Sub InsertCols()
Dim vHdrs As Variant, v As Variant, n& 'as Long
Const sHeaders As String = "SUM,PRODUCT,QUOTIENT,SUMPRODUCT"
Const sFormulas As String = _
"=SUM(1,1):=PRODUCT(1,1):=QUOTIENT(1,1):=SUMPRODUCT(1,1)"
vHdrs = Split(sHeaders, ","): v = Split(sFormulas, ":")
Const x As String = "SUM" '//for testing only
With Sheet1
If x = vHdrs(0) Then
.Columns("C:F").Insert
.Range("B1").Resize(, 4) = vHdrs
For n = LBound(v) To UBound(v)
.Range("B2").Offset(, n).Formula = v(n)
Next 'n
End If 'x = vHdrs(0)
End With 'Sheet1
End Sub
 
Dim i As Integer

With Sheet1
For i = 1 To 4
.Columns(2).Insert shift:=xlToRight
Next i
.Range("B1").Resize(1, 4).Value = _
Array("SUM", "PRODUCT", "QUOTIENT", "SUMPRODUCT")
.Range("B2").Resize(1, 4).Value = _
Array("=SUM(1,1)", "=PRODUCT(1,1)", "=QUOTIENT(1,1)",
"=SUMPRODUCT(1,1)")
End With
 
Garry,

Clearly my coding skills aren't advanced, but I managed to get the
following working.

Here's a snippet:

With Sheet1
Application.Calculation = xlCalculationManual

If y = "SUM" And .Range("B3") = "-1" Then
.Columns("B:E").Insert
.Range("B3:E3") = Sheet3.Range("B1:E1").Value
.Range("Details_Lookup_Data") = "=Details_Lookup_Formula"
End If

Application.Calculation = xlCalculationAutomatic
End With


Your use of the Columns.(...).Insert function allows four columns to be
added at once.

And I used the logic of the "=Fill_Formula" solution you helped me with
to fill a range with the "_Details_Lookup_Formula" name using relative
addresses.

Believe it or not, I've learned to code (from no experience) from people
like yourself.

Thanks again !

Happy Code-aween !


- Ronald K.
 
Back
Top