Insert columns and values programmatically with IF statement
 

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.

Insert columns and values programmatically with IF statement
 

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

Insert columns and values programmatically with IF statement
 

Don,

Thanks for the quick response.

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



- Ronald K.

Insert columns and values programmatically with IF statement
 

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

Insert columns and values programmatically with IF statement
 

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

Insert columns and values programmatically with IF statement
 

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

Insert columns and values programmatically with IF statement
 

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.

Insert columns and values programmatically with IF statement
 

Tim,

Thanks for the help.

I now know how to use the ARRAY function.



- Ronald K.

Insert columns and values programmatically with IF statement
 

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.

Insert columns and values programmatically with IF statement
 

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