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.

On Feb 26, 6:38 pm, "merjet" wrote:
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.


Dear Merjet,

As told by you i tried to add the same in the worksheet's code
module.

But this is for your information that i have one more Macro running in
that sheet as per below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bFound As Boolean
Dim iEnd As Integer
Dim c As Range
Dim rng As Range

If Target.Column = 17 Then
If Target = "" Then
Target.Offset(0, -15) = ""
Target.Offset(0, -14) = ""
Target.Offset(0, -9) = ""
Else
iEnd = Sheets("EVALUATION").Range("A2").End(xlDown).Row
Set rng = Sheets("EVALUATION").Range("A2:A" & iEnd)
For Each c In rng
If Target = c Then
Target.Offset(0, -15) = c.Offset(0, 2)
Target.Offset(0, -14) = c.Offset(0, 1)
Target.Offset(0, -9) = c.Offset(0, 6)
bFound = True
Exit For
End If
Next c
If bFound = False Then
MsgBox ("Ref No not found.")
Target = ""
End If
End If
End If

End Sub

Now when i try to copy the code provied by you which is as below:

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

its giving me error as:
Ambigious Name Detected Worksheet_Change

Pls help me if you can explain me how to club the two Macro under
Worksheet_Change.

Awaiting for your response.

Thanks in Advance.

Regards

Akash

Combine the two. Delete the last line of the first one -- End Sub --
and the first line of the second one -- Private Sub
Worksheet_Change(ByVal Target As Range).

Merjet