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.

On Oct 16, 8:06*am, "kittronald" wrote:
* * 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

Don,

Thanks for the quick response.

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



- Ronald K.

On Oct 16, 9:50*am, "kittronald" wrote:
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................

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):=SUMPRODUC T(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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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):=SUMPRODUC T(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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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


On Oct 16, 6:06*am, "kittronald" wrote:
* * 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.

Tim,

Thanks for the help.

I now know how to use the ARRAY function.



- Ronald K.

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.

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc