Squareroot function for a value by macro
Hi, I have a number (e.g) A Column 150, this number multiply by 1.2727 & getting answer we should use SQUREROOT, The answer will be in B Column.This only by macro!
Last edited by friendlykashish; 06-11-2011 at 12:27 AM.
hello
you can try to use function sqr(value)
copy below function into module and test it
then put in B1 formula "=squre(A1)"Function squre(v As Range) As Double squre = Sqr(v.Value * 1.2727) End Function
it should works
Best Regards
I try, but didn't work
Originally Posted by maczaq
see attached file, I hope it will work
Best Regards
Thanks. But if possible the formula( =squre(A1) directly add into macro.(Not in Excel Column )
it is possible but you have to know a condition which tell to the program when this value should be calculate (for example for any change in sheet1, or when someone select some cell, on workbook open, sheet select, etc,)
tell me what event will start procedure.
Best Regards
I will clearly explain with excel attachmet. Please find attachment.
Thanks, Kind Regards,
Sorry, in my reply there is no attachment.
I will clearly explain with excel attachmet. Please find attachment.
Thanks, Kind Regards,
Put above code in a module in a copy of your workbook and run it.Sub TryThis() With Range("C2", Range("C" & Rows.Count).End(xlUp)).Offset(, 2) .FormulaR1C1 = "=Sqrt(RC[-2]*RC[-1])" .Value = .Value .NumberFormat = "0.00" End With End Sub
See if that is what you want
Regards.
John
Wonderful & excellent,this is exactly i expected and one more thing i have some other caluculation in other columns. how to do columnwise calculations. please find attachement in H column! there is list in H 2 column, if i select 10, 100 should come in I 2 column, the same way 11-200, 12-300 ,13-400 respectively.
Thanks.
Last edited by friendlykashish; 06-10-2011 at 01:41 AM. Reason: spelling mistake, corrected
Hi friendlykashish,
In attachment you will find my solution.
Sensitive for changes on column A & B
All next data calculate automaticaly.
Best Regards
Excellent.
Thanks.
update code
Best RegardsPublic r As Long Private Sub Worksheet_Change(ByVal Target As Range) 'check if you fill column A & B in current row r = Target.Row If Target.Column = 1 Or Target.Column = 2 Then If Cells(r, 1).Value <> "" And Cells(r, 2).Value <> "" Then area = Cells(r, 2).Value / Cells(r, 1).Value / 0.00785 'area cons = 1.2727 'const value Cells(r, 3).Value = area Cells(r, 4).Value = 1.2727 Cells(r, 5).Value = Sqr(area * cons) 'sqrt i = i + 1 End If ElseIf Target.Column = 8 Then 'change in column H If Cells(r, 8).Value > 9 Then Cells(r, 9).Value = (Cells(r, 8).Value - 9) * 100 Else Cells(r, 9).Value = "" End If End If End Sub
Last edited by maczaq; 06-10-2011 at 07:11 AM. Reason: update
IT'S WORKS GREAT, Please find my attachment for One more modification in H column,
8, 10,12, 14, 16, 18, 20 ,22, 25, 28, 30, 32, 36, 40 & I column 50.27, 78.54, 113.1, 153.9 201.1 254.5, 314.2, 380.1, 490.9, 615.8, 706.9, 804.2, 1017.4, 1256.6 .
IF i select 8 in "H" column, 50.27 should come in "I" column. 10 -- 78.5 like this.
Kind Regards,
Last edited by shg; 06-10-2011 at 10:42 AM. Reason: deleted quote
here you are
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks