Insert columns and values programmatically with IF statement

[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.

 

[Don Guillett]

    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.

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

 

[kittronald]

Don,

Thanks for the quick response.

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



- Ronald K.

 

[Don Guillett]

Don,

    Thanks for the quick response.

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

- Ronald K.

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................

 

[GS]

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

 

[GS]

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

 

[Tim Williams]

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

 

[kittronald]

Tim,

Thanks for the help.

I now know how to use the ARRAY function.



- Ronald K.

 

[kittronald]

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.

 

[GS]

Glad to be a help! Also, glad to see you enjoying VBA...