Rew Macro For Computation

A

Akash

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
 
M

merjet

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
 
A

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
Click to expand...

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
 
A

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
Click to expand...


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
 
M

merjet

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
 
A

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
Click to expand...


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
 
M

merjet

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.
Click to expand...

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

Akash

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


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
 
M

merjet

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
 
A

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
Click to expand...

thanks a tonn

it starts working....

thank you very much
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data of Sheet1 should gets autometically posted in Sheet2 2
Macro Required for Lookup Function 3
Macro for Creating Word Document!! 5
Sheet1 Data Autometically Uploaded in Sheet2 2
Retreiving Sheet1 Data Autometically Uploaded in Sheet2 2
Query of List Option 1
Retrieving Data from Sheet1 into Sheet2 1
IF FUNCTION 2

Top