How to update create and update formula based column on excel sheet with macro.

Hi Praveen,

Try below:

Sub AddFormulas()
Dim Ws As Worksheet
Dim LRow As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Volumetrics")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Ws.Range("AN2:AN" & LRow).FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""GIP"",R[2]C10)),1,0)"
Ws.Range("AO2:AO" & LRow).FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""TV"",R[2]C10)),1,0)"
Ws.Range("AP2:AP" & LRow).FormulaR1C1 = "=IF(R[2]C9=""NO"",0,1)"
Ws.Range("AQ2:AQ" & LRow).FormulaR1C1 = "=IF(R[2]C21=""NO"",0,1)"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

Select allOpen in new window

Final_Planning_v2.xlsm

Hi Shums,

It's working fine.
I need two more changes on this.

1) Can we also add the column name for it...Here
AN - WGS
AO - TV
AP - SHORT
AQ - DUE

2) I have three more spread sheet in the same workbooks , But these columns need to create between other columns..
How can we identify through the code and update the formula based column..

Pricing
D =IF(ISBLANK(B5),C5,B5)
Q =IF(AND(I6>0,J6>0),"DIR AND IND",IF(AND(I6>0,J6=0),"DIR ONLY",IF(AND(I6=0,J6>0),"IND ONLY",IF(AND(I6=0,J6=0),"FREE"))))
X =I6*(100-R6)/100

Worksheet : Progen
Q  =MIN(K3:P3)

Worksheet : usage2
K =IF(H4>0,1,0)
L =IF(I4>0,1,0)
M =IF(J4>0,1,0)
N =SUM(K4:M4)
O =SUM(H4:J4)/3

Worksheet : Metrics By Stocking Reason
S =D4/E4

Worksheet : Replacement NDC 3
G =VLOOKUP(F4,Usage2!$A$4:$C$1789,3,FALSE)

3) if the worksheet having the pivottable data.. this code will work?

I have attached the spread sheet .. Please help me

Thanks,
Praveen
final_planning2.xlsm

Hi Praveen,

I will reply later. I am away from laptop.

Thank you

Another Issue also am getting below line of code..

Ws.Range("I4:I" & LRow).FormulaR1C1 = "=VLOOKUP(R[0]C6,Usage2!$A$4:$C$1789,3,FALSE)"  <-- this one correct?

this is the original formula...
VLOOKUP(F4,Usage2!$A$4:$C$1789,3,FALSE)

No.

This is not the correct way, you need to transforming Excel Formula into VBA Formula.

I am back, let me finish above changes, then I will make other changes.

Thank you.

In which sheet you are using this formula?
VLOOKUP(F4,Usage2!$A$4:$C$1789,3,FALSE)
I dont see sheet named usage2 , Metrics By Stocking Reason & Replacement NDC 3

That one is different sheet..let n

Even the formula doesn't interpret your expected result.

For eg: Pricing sheet, from row 5 there is no data at all and your formula is from B5 & J5

Please upload relevant sample as per your required changes.

Please try below with 1st change with Column Header.

Sub AddFormulas()
Dim Ws As Worksheet
Dim LRow As Long
Application.ScreenUpdating = False
Set Ws = Worksheets("Volumetrics")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Ws.Range("AN1").Value = "WGS"
Ws.Range("AO1").Value = "TV"
Ws.Range("AP1").Value = "SHORT"
Ws.Range("AQ1").Value = "DUE"
Ws.Range("AN2:AN" & LRow).FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""GIP"",R[2]C10)),1,0)"
Ws.Range("AO2:AO" & LRow).FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""TV"",R[2]C10)),1,0)"
Ws.Range("AP2:AP" & LRow).FormulaR1C1 = "=IF(R[2]C9=""NO"",0,1)"
Ws.Range("AQ2:AQ" & LRow).FormulaR1C1 = "=IF(R[2]C21=""NO"",0,1)"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

Select allOpen in new window

Please post relevant sample, I will provide code tomorrow, its getting late here.

Sure. Thank you

All the issues are resolved except the following issue..

For Ex::
I have a work sheet with following columns.

A    B    C     D
here B is formula column.

Through the vba code, I need to create the B column.

Before
A    B  C
after
A   B   C(B)  D(C)

Please help on this issue...

Thank you.

Hi Praveen,

This is another issue, please attach a sample.

Hi,

I will send you  in 10 min

Thanks

Hi
Please check attached sheet.
This is my final requirement , Please help me on this
 
In this attached spread sheet I have three sheets
M, Priciing and QD

In the QD - I need add a button name ' update'
when the user press the button -- QD > column C Values  -- need to send to Sheet 'M' and then process the formula column in 'M' Sheet.

Here is the example::
In QD Sheet > Column 'C'  pallet size new value 67 ( old is 50 in column B) .... When user press the button 'Update' ....we need to take this value
and change in sheet 'M'  column 'V'  Where you can see the '50' in row 6.... we need to change it to '67' then process the formula on this column...

Let me know if you have any question.
final_planning2.xlsm

Check attached...
Hope this is last change.
You need to be specific in your initial question, every time you start with something and end up with something different.
final_planning_v4.xlsm

Sorry shums.. Thank you.

Let me check and update you

Regards,
Praveen

It's working fine... Thank you.

Can you please help me on my previous question. When we  want to create the formula based column. How we can change existing columns and create the formula column..

example..in the pricing sheet.

We need to create the D column.. but the sheet is already  'D' column....
we need to create ''D' Column and all other columns need to change like ... D > E, E > F and F>G.....and so on.........

Pricing
D =IF(ISBLANK(B5),C5,B5)

Thank you ,
Praveen

Thank you

Pleased to help

Hi Shums,

Can you please help me this excel to vba for this formula.. I was tried , But in the runtime iam getting error..

excel
=IFERROR(VLOOKUP($D10,OL!$D$3:$AW$44546,30,FALSE),"")
=IFERROR(VLOOKUP($D10,OL!$D$3:$AW$44546,31,FALSE),"")

OL is the spreadsheet .....

Regards,
Praveen