Solved

VBA to find and move

Posted on 2016-08-12
3
54 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 30

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 30

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

820 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