Hi,

I have a sheet in which i have few columns


1. FH
2. FL
3. RD
4. FPI
5. QTY
6. COMPUTE

NOW I WANT THAT AS SOON AS I THE USER ENTER THE VALUES IN THE COLUMNS
(FH, FL, RD, FPI, QTY) IT SHOULD COMPUTE THE BELOW MENTIONED FORMULAE.

COMPUTE = IF(TRIM(D8)="","",(((FH*FL*RD)/144*QTY)))

HOW CAN I DO THIS WITH THE HELP OF THE FORMULAE.
I WANTED THIS FUNCTION AS THE FORMULAE GETS DELETED BY THE USERS AND
THEN I HAD TO CHECK OUT THE ENTIRE FORMAT TO RECTIFY THE DATA.

PLS DO HELP ME IN THIS REGARDS

THANKS IN ADVANCE

AKASH

Put the following in the worksheet's code module. I assumed your data
is in cols A-F; you will have to adjust the code if not. The lines are
long, so I expect they will wrap and you will need to rectify that
after you copy and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Or
Target.Column = 5 Then
If Range("A" & Target.Row) < "" And Range("B" & Target.Row)
< "" And Range("C" & Target.Row) < "" And Range("E" & Target.Row) <
"" Then
Range("F" & Target.Row).FormulaR1C1 =
"=IF(ISBLANK(R8C4)=FALSE,RC[-5]*RC[-4]*RC[-3]/144*RC[-1])"
End If
End If
End Sub

Hth,
Merjet

On Feb 23, 7:20 pm, "merjet" wrote:
Put the following in the worksheet's code module. I assumed your data
is in cols A-F; you will have to adjust the code if not. The lines are
long, so I expect they will wrap and you will need to rectify that
after you copy and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Or
Target.Column = 5 Then
If Range("A" & Target.Row) < "" And Range("B" & Target.Row)
< "" And Range("C" & Target.Row) < "" And Range("E" & Target.Row) <
"" Then
Range("F" & Target.Row).FormulaR1C1 =
"=IF(ISBLANK(R8C4)=FALSE,RC[-5]*RC[-4]*RC[-3]/144*RC[-1])"
End If
End If
End Sub

Hth,
Merjet

Hi,

Thanks for the Support but i got stuck up in this line. Its now
working. More over i am not able to understand the below mentioned
code:

Range("F" & Target.Row).FormulaR1C1 =
"=IF(ISBLANK(R8C4)=FALSE,RC[-5]*RC[-4]*RC[-3]/144*RC[-1])"

pls help.

Akash

On Feb 23, 7:20 pm, "merjet" wrote:
Put the following in the worksheet's code module. I assumed your data
is in cols A-F; you will have to adjust the code if not. The lines are
long, so I expect they will wrap and you will need to rectify that
after you copy and paste.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Or Target.Column = 2 Or Target.Column = 3 Or
Target.Column = 5 Then
If Range("A" & Target.Row) < "" And Range("B" & Target.Row)
< "" And Range("C" & Target.Row) < "" And Range("E" & Target.Row) <
"" Then
Range("F" & Target.Row).FormulaR1C1 =
"=IF(ISBLANK(R8C4)=FALSE,RC[-5]*RC[-4]*RC[-3]/144*RC[-1])"
End If
End If
End Sub

Hth,
Merjet


Hi,

In my sheet i have values as below:

FH is in D Column
FL is in E Column
RD is in F Column
QTY is in H Column
COMPUTE IS IN K COLUMN

PLS HELP ME IN TJIS REGARD

AKASH

I changed the column references. The Sub is now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Or
Target.Column = 8 Then
If Range("D" & Target.Row) < "" And Range("E" & Target.Row)
< "" And Range("F" & Target.Row) < "" And Range("H" & Target.Row) <
"" Then
Range("K" & Target.Row).FormulaR1C1 =
"=IF(ISBLANK(R8C4)=FALSE,RC[-7]*RC[-6]*RC[-5]/144*RC[-3])"
End If
End If
End Sub

R8C4 refers to row 8, column 4, i.e. D8. RC[-7] refers to the cell in
the same row and 7 columns left of column K. Similar for the others.

Hth,
Merjet

On Feb 24, 6:08 pm, "merjet" wrote:
I changed the column references. The Sub is now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Or
Target.Column = 8 Then
If Range("D" & Target.Row) < "" And Range("E" & Target.Row)
< "" And Range("F" & Target.Row) < "" And Range("H" & Target.Row) <
"" Then
Range("K" & Target.Row).FormulaR1C1 =
"=IF(ISBLANK(R8C4)=FALSE,RC[-7]*RC[-6]*RC[-5]/144*RC[-3])"
End If
End If
End Sub

R8C4 refers to row 8, column 4, i.e. D8. RC[-7] refers to the cell in
the same row and 7 columns left of column K. Similar for the others.

Hth,
Merjet


Dear Merjet,

Its not working...

Nothing is happening.

i tried but its not working

what should i do in this regards

I have following columns in my entire format


Sr No in A Column
Drg No in B Column
Prioject Name in C Column
FH is in D Column
FL is in E Column
RD is in F Column
FPI is in G column
QTY is in H Column
Type is in I Column
Area in J Column
COMPUTE IS IN K COLUMN
Analyze in L Column
Coil Type in M Column
Header Size in N Column
Weight in Kg in O Column
Total Weight in P Column
Ref No: in Q Column

Now i want that the below mentioned formulae in the Compute Column (K)

FH*FL*RD)/144*QTY

Whatever macro provided by you is not working in my seet. No error is
getting reflect.

I tried to added the macro provided by you i in Module 2.

But its not working.

What should i do in regard to above mentioned probs.

Awaiting for your reply as i had to start the work in the new format
as soon as possible.

Pls do help me in this regards

Thanks in Advance.

Akash

Whatever macro provided by you is not working in my seet. No error is
getting reflect.

I tried to added the macro provided by you i in Module 2.

But its not working.

As I said in #2, put it in the worksheet's code module.