[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA to find and move

Posted on 2016-08-12
3
Medium Priority
?
61 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 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 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 33

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

873 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