Sorry...correction. use this.
Public Function CalculateType(Rng As Range) If Rng.Value = 300 Or Rng.Value = 550 Or Rng.Value = 800 Or Rng.Value = 1050 Or Rng.Value = 1300 Or Rng.Value = 1550 Then CalculateType = 50 ElseIf Rng.Value = 750 Then CalculateType = 250 ElseIf Rng.Value = 1000 Or Rng.Value = 2000 Or Rng.Value = 3000 Then CalculateType = Rng.Value Else CalculateType = Rng.Value + 50 End If End Function On Tue, Jun 28, 2011 at 2:01 PM, Vasant <vasant...@gmail.com> wrote: > try this. > > use this UDF for the type columns. Pass the Previous Type value into > the function > > > Public Function CalculateType(Rng As Range) > If Rng.Value = 300 Or Rng.Value = 550 Or Rng.Value = 800 Or Rng.Value > = 1050 Or Rng.Value = 1300 Or Rng.Value = 1550 Then > CalculateType = 50 > ElseIf Rng.Value = 750 Then > CalculateType = 250 > ElseIf Rng.Value = 1000 Or Rng.Value = 2000 Or Rng.Value = 3000 Then > CalculateType = Rng.Value > End If > End Function > > > On Tue, Jun 28, 2011 at 11:34 AM, Subhash Yadav <scy2...@gmail.com> wrote: >> Please help me out. >> >> >> >> On Tue, Jun 21, 2011 at 10:09 AM, Subhash Yadav <scy2...@gmail.com> wrote: >>> >>> Dear Experts, >>> >>> Please refer my attached file for which i want solution. >>> >>> My queries are: >>> >>> 1. In the "type" column - Value is "previous type+50". but if the values >>> are 300,550,800,1050,1300,1550 etc this start again from 50. 750 should be >>> 250. 1000, 2000, 3000 should remain same. >>> >>> 2. Which ever cell is blank in the "Carried out HMR / KMR" the adjacent >>> (earlier) two cell data should come in next months MS-1 " Type and Due HMR / >>> KMR" against every asset code. >>> >>> Thank in advance to you. >>> >>> -- >>> Subhash Chand Yadav >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >> >> >> >> -- >> Subhash Chand Yadav >> >> -- >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > > > -- > Regards > > Vasant > > skype Id: vasantjob > vasant...@gmail.com > -- Regards Vasant skype Id: vasantjob vasant...@gmail.com -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel