PDA

View Full Version : Solved: Insert columns in multiple sheets along with formula in a specified row numbers.



ram117
11-30-2005, 11:45 PM
Hi EveryBody:hi: ,


I have 10 to 12 sheets in a workbook, I need to insert 3 columns between Column E&F along with given formula stating from row 9 but the last row number for the formula differs in each sheet it depends on the data in Column D The last row no of the column D which has the last entry will be the last row No. for the new columns where the formula to be inserted. Here I'm Giving an example for better understanding.

In sheet1 I have data in column D till row No 24 Now In sheet1 three new columns should be inserted and also formula(say sum(d2:c2)) should be present in the new column from row No 9 to the row No 24.

Like wise in sheet2 The last entry in column D is in row No 35

If any body have suggestions Pl. tell me.

Thanks and Regards

Ramana

Rembo
12-02-2005, 05:16 AM
Hello Ramana,

Place the following code in a module and run the macro InsertColsAndFormulas. It inserts columns and formulla's on every worksheet in your workbeek.

Note that I used the Formula property in this routine to insert the formula in a cell. By doing so the formula is entered exactly as I typed it; in each cell it will show the sum of C2 and D2.
If you would like it to behave like with relative referencing you have to rewrite the formula or use the property FormulaR1C1 property.

If you have any problems with the formula just let me know and I'll sort it for you.

Rembo


Sub InsertColsAndFormulas()
Dim ws As Worksheet
Dim rCell As Range
Dim l As Long, lLastrow As Long
For Each ws In Worksheets
With ws
lLastrow = .Range("D1").End(xlDown).Row
.Columns("E:G").Insert Shift:=xlToRight
For Each rCell In .Range("E1:G" & lLastrow)
rCell.Formula = "=SUM(C2:D2)"
Next rCell
End With
Next ws
End Sub

ram117
12-03-2005, 02:23 AM
Hi Rembo,

Thankyou for the reply it is working, bit in the other way. My problem is I'm having data in all the columns so I wanted insert a new column between E&F(now the new column will be F and the old F will offset right and becomes G) Then write the formula with relativity in each sheet in the newly inserted column referring cells in the column D(because the data in column D in all the sheet doesn't start at a particular row or end at particular row, So the formula should be inserted only where the column D has DATA. And the code you have given is inserting formula in Three columns E,F&G but I need only in Newly inserted column F. I think you will understand my problem if it is unclear Pl tell me.

Thanks and Regards

Ramana

Rembo
12-03-2005, 08:01 AM
Hello Ramana,


My problem is I'm having data in all the columns so I wanted insert a new column between E&F(now the new column will be F and the old F will offset right and becomes G)

Ok, so you want just one new column F to be inserted? That's easy enough because all you have to do is change .Columns("E:G").Insert to .Columns("F:F").Insert


Then write the formula with relativity in each sheet in the newly inserted column referring cells in the column D(because the data in column D in all the sheet doesn't start at a particular row or end at particular row, So the formula should be inserted only where the column D has DATA.

As mentioned in my previous post just use the FormulaR1C1 property instead of the Formula property. All you have to do now is test if there's a value in column D and if so write the formula in column F. If there isn't a value in column D do nothing and move on to the next cell in column D.

Note that I changed the method to determine the last row of data in column D. If your data isn't contiguous (not seperated by empty cells) you can determine the last row with data like shown in the code below.

An example: with the above in mind, say you wanted to add a formula in cell F1 that sums up the values in cells C1 and D1, then in F2 a formula that sums up cells C2 and D2 etc.

In code that looks like this:


Sub InsertColsAndFormulas()
Dim ws As Worksheet
Dim rCell As Range
Dim lLastrow As Long
For Each ws In Worksheets
With ws
.Columns("F:F").Insert Shift:=xlToRight
lLastrow = .Range("D65536").End(xlUp).Row
For Each rCell In .Range("F1:F" & lLastrow)
If rCell.Offset(0, -2).Value <> "" Then
rCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-2])"
End If
Next rCell
End With
Next ws
End Sub

Hope that helps,

Remco

ram117
12-14-2005, 03:46 AM
Hi Rembo,

Thanks a lot for the code Its working perfect for the Sum formula Now I'm Trying it to expand i.e. to insert many columns and different formulas in the inserted column based on your code.If have any problem I will post the code.

Thanks and Regards

Ramana

ram117
12-27-2005, 04:26 AM
Hi Rembo,

Again I stukup with this code. The code you have given is working for the formulas which doesn't have the double quotes if the formula itself contains a double quote then it is taking the second double quote as expected end of statement. Please can you have a look into this and solve the problem. Here I'm giving the code what I'm trying o do.


Sub InsertColsAndFormulas()
Dim ws As Worksheet
Dim rCell As Range
Dim lLastrow As Long
For Each ws In Worksheets
With ws
.Columns("F:F").Insert Shift:=xlToRight
lLastrow = .Range("D65536").End(xlUp).Row
For Each rCell In .Range("F1:F" & lLastrow)
If rCell.Offset(0, -2).Value <> "" Then
rCell.FormulaR1C1 = _
"=mid(RC[-3],find("(",RC[-3],1)+1,find(")",RC[-3],1)-find("(",RC[-3],1)-1)"
End If
Next rCell
End With
Next ws
End Sub


The problem is in the line rcell.formulaR1C1= "=mid(RC[-3],find("(",......

It is telling that expected end of statement.

Thanks and Regards

Ramana :banghead:

Bob Phillips
12-27-2005, 08:56 AM
rCell.FormulaR1C1 = "=mid(RC[-3],find(""("",RC[-3],1)+1,find("")"",RC[-3],1)-find(""("",RC[-3],1)-1)"

ram117
01-01-2006, 09:15 PM
Hi Xld,

First of all I wish you and the other forum members a very happy a nd prosperius new year.
Thanks a lot for the reply its working perffect.

Regards

Ramana

Bob Phillips
01-02-2006, 05:29 AM
Hi Xld,

First of all I wish you and the other forum members a very happy a nd prosperius new year.
Thanks a lot for the reply its working perffect.

Regards

Ramana

It's a pleasure Ramana. And a happy new year to you.