Solved

VBA to find and move

Posted on 2016-08-12
3
48 Views
Last Modified: 2016-08-12
Can an expert help me out with this please. I have attached a file that should make it clear.

Check Col ‘V’ for any amounts other than zero.

If there is an amount select the cell below the amount then insert a new row
Move the amount from ‘V’ and put into the cell below in ‘U’
Move the data from ‘R’ and put into the cell below in ‘Q’
Copy the data in ‘P’ and put in cell below.

Repeat for any other amounts.

then

Check Col ‘W’ for anything other than zero’s
Select the cell below the amount then insert a 2 new rows
Copy the amount and put into the new row in ‘U’ as a minus.
Copy the data in P,Q and put in line below
Now select the original amount and move it under the minus amount [as a plus]

Move the data from ‘R’ and put that in the ‘Q’ [2 lines below]
Copy the data from P in the original row and put into ‘P’ in the blank cell.

Repeat for any other amounts.
Find-and-Move.xlsx
0
Comment
Question by:Jagwarman
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41753472
Try something like this......

Sub ReArrangeData()
Dim Rng As Range, Cell As Range
Dim lr As Long, i As Long

Application.ScreenUpdating = False
lr = Cells(Rows.Count, "V").End(xlUp).Row
For i = lr To 2 Step -1
   If Cells(i, "V") <> 0 Then
      Rows(i + 1).Insert
      Cells(i, "V").Select
      Cells(i, "P").Copy Cells(i + 1, "P")
      Cells(i, "R").Cut Cells(i + 1, "Q")
      Cells(i, "V").Cut Cells(i + 1, "U")
   End If
Next i

lr = Cells(Rows.Count, "W").End(xlUp).Row
For i = lr To 2 Step -1
   If Cells(i, "W") <> 0 Then
      Rows(i + 1).Resize(2).Insert
      Cells(i, "P").Copy Cells(i + 1, "P").Resize(2)
      Cells(i, "Q").Copy Cells(i + 1, "Q").Resize(2)
      Cells(i, "R").Cut Cells(i + 2, "Q")
      Cells(i + 1, "U") = Cells(i, "W") * -1
      Cells(i + 2, "U") = Cells(i, "W")
      Cells(i, "W").ClearContents
   End If
Next i
Application.ScreenUpdating = True
MsgBox "Task Completed.", vbInformation
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41753531
not something like that exactly like that. Brilliant many thanks
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41753574
You're welcome. Thanks for the feedback.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now