Thanks a lot Ashish but this doesn't do what i need. I want the available quantity and amount against negative and zero values. In other words I want to set off those negatives and nulls with the positives.
Thanks & regards, AJ On Wed, Jul 6, 2011 at 12:39 PM, ashish koul <koul.ash...@gmail.com> wrote: > Option Compare Text > Sub test() > Dim i, j, w As Long > w = Sheets(1).Range("a65356").End(xlUp).Row > For i = 2 To w > > > If Range("e" & i).Value = 0 And Range("f" & i).Value < 0 Then > > Range("h" & i).Value = Range("d" & i).Value > Range("d" & i).Value = "" > > Range("i" & i).Value = Range("e" & i).Value > Range("e" & i).Value = "" > Range("j" & i).Value = Range("f" & i).Value > Range("f" & i).Value = "" > > Range("k" & i).Value = Range("g" & i).Value > Range("g" & i).Value = "" > > > For j = 2 To w > > > > > If j <> i And Range("c" & j).Value = Range("c" & i).Value Then > If Range("e" & j).Value <> 0 And Range("f" & j).Value >= 0 Then > Range("d" & i).Value = Range("d" & j).Value > > Range("e" & i).Value = Range("e" & j).Value > Range("f" & i).Value = Range("f" & j).Value > Range("g" & i).Value = Range("g" & j).Value > Exit For > End If > > End If > > Next j > > > End If > Next i > > > > End Sub > > > On Wed, Jul 6, 2011 at 11:06 AM, Abhishek Jain <abhishek....@gmail.com>wrote: > >> Friends, >> >> Any update on this? >> >> Thanks, >> >> AJ >> >> >> On Tue, Jul 5, 2011 at 11:49 AM, Abhishek Jain <abhishek....@gmail.com>wrote: >> >>> Dear friends, >>> >>> Attached along a data wherein in some rows, quantity and amount for items >>> nos are both positive which is my inventory. >>> >>> In other rows one of the either or both are negative or nil. What I need >>> to do is to adjust the negative and nil values from the positive values >>> available for that particular item no. The macro should search for the >>> negative/zero values in quantity and amount columns and if it is found in >>> either of the column it should shift the available quantity/amount (with >>> rate) to the adjacent columns and in the rows from where the data is shifted >>> the row address of destination is written. >>> >>> An Example - >>> >>> At Row 16 - The quantity is 0 and amount is negative for item no. >>> 002001010001. Now for this item no., the positive values are available at >>> row no. 14. The macro should cut the Location Code, Quantity, Amount and >>> Rate (D14:G14) and paste it in H16:K16) and in D14 the destination row no. >>> is written - H16. (probably with different color). >>> >>> Hope some one helps me with this. This is a bit urgent. >>> >>> Thanks & regards, >>> >>> AJ >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> 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 >>> >> >> -- >> >> ---------------------------------------------------------------------------------- >> 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* > * * > *Ashish Koul* > *akoul*.*blogspot*.com <http://akoul.blogspot.com/> > http://akoul.posterous.com/ > *akoul*.wordpress.com <http://akoul.wordpress.com/> > My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> > > > P Before printing, think about the environment. > > > -- > > ---------------------------------------------------------------------------------- > 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 > -- ---------------------------------------------------------------------------------- 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