Module to find & move based on cell adjacent's set value

[bradnailer]

ABCDE
FGHIJK


If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better!!! The output in the example above would be:

ABCDEG
F HIJK <----then this row gets deleted

So essentially, whenever the macro comes across the value "product" it move the data in the cell adjacent to it up a row and over 4 columns and then the row it came from is deleted.


I've looked and searched but haven't seen anything that I may be able to copy (I'm not a VBA expert so I'm not sure how to write something myself)

Thanks!!

 

[Datsmart]

This code will do what you are asking:

Sub FooRng()
    Dim Rng As Range
    Dim c As Range
    Dim testvalue
    testvalue = "product"
    Set Rng = Range("B1:B" & Range("B65536").End(xlUp).Row)
    For Each c In Rng
        If c.Value = testvalue Then
            c.Cut c.Offset(-1, 4)
            c.Offset(1, 0).EntireRow.Delete
        End If
    Next c
End Sub

This assumes your data to move is always in column B. If not, change Rng code as needed.

 

[bradnailer]

awesome!!! I'm going to try it!!

Do I simply create a macro and then insert that code in it? I'm not too experienced in macros BUT I'm a quick learner!!!

Thx again!!

 

[Datsmart]

How to use posted code (standard module)
To use the posted code;
Start the Visual Basic Editor (via Menu Tools, Macro, Visual Basic Editor, or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste your code
Note: All Macros start with "Sub MacroName()" and End with "End Sub"

How to create a button and assign a macro to it:
If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialogue box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name and clicking Run.

 

[bradnailer]

I can't thank you enough if this all works how I hope in my mind I want it to - I'll let you know!!!

This place is so cool, I can't believe I didn't find it earlier

 

[bradnailer]

a bit of a hiccup

Here's a more real example of what my sheet (simplified by deleting lots of rows for viewing sake) looks like:

test lookups.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	ID #	State	Brand	Product	Complaint	Code Date	Size	Qty	Incident Date:
3
4
5
6	4210	WA	AppleGlen	Juice - Apple	Sour	JL/3/2007	4 L	8.00 L	06/12/2007
7	Incident Description:	sour upon opening
8	Incident Follow-up:	shelf life sample was ok at code
9	Corrective Actions:	check production sheets
10
11
12
13
Sheet1

Here's the code in used for the button:

Sub FooRng()
Dim Rng As Range
Dim c As Range
Dim testvalue
testvalue = "Incident Description:"
Set Rng = Range("B1:B" & Range("B65536").End(xlUp).Row)
For Each c In Rng
If c.Value = testvalue Then
c.Cut c.Offset(-1, 8)
c.Offset(1, 0).EntireRow.Delete
End If
Next c
End Sub

First, nothing appeared to work when I clicked the button so I changed "B1:B" to "A1:A" and the macro moved all instances of "Incident Description:" itself to the cells where I wanted the info that was actually in the cell to the right of Incident Description:

If it worked, I was hoping to figure out how to do the rest I wanted to do but I'll describe:

In the sheet I want to:

-move the data in the cell to the right of "Incident Description:" up 1 row and over 8 columns (then delete the row),

-move the data in the cell to the right of "Incident Follow-up:" up 2 rows and over 9 columns (then delete the row),

-move the data in the cell to the right of "Corrective Actions:" up 3 rows and over 10 columns (then delete the row).

Essentially I want all the info pertaining to the specific incident in one row so I can manipulate the data better myself.

There will be multiple incidents but I only copied one as an example.

If it matters, there may not always be a "Incident follow-up:" or a "Corrective Actions:" but I don't think that matter as it just won't be found in the search?

I hate being a info leech but I'm really out of my league in the VBA language needed and I've spent hours trying things out myself.

Thanks so much in advance if you are able to help out!!

 

[Datsmart]

This code change works on your sample data.

Sub FooRng2()
Dim Rng As Range
Dim c As Range
Dim i As Integer
Dim tv As String
Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row)
For i = 1 To 3
    If i = 1 Then tv = "Incident Description:": os = 8  'os is Column OffSet value
    If i = 2 Then tv = "Incident Follow-up:": os = 9
    If i = 3 Then tv = "Corrective Actions:": os = 10
    For Each c In Rng
        If c.Value = tv Then
        c.Cut c.Offset(-1, os)
        c.Offset(1, 0).EntireRow.Delete
        End If
    Next c
Next i
End Sub

The "For i = 1 To 3" loop changes the testvalue (tv) and the Column OffSet number (os) variables when the code runs. This determines what text value to search for and which column to Cut and Paste the data to.

 

[bradnailer]

It's almost working - when I click my button, what gets moved is the actual text "Incident Description:", "Incident Follow-up:" & "Corrective Actions:" instead of the data in the cells adjacent to those.

I've played around with your code to see if its just a small change but not yet? Any ideas?

 

[bradnailer]

Hey John,

Is the coding that you are helping me with considered beginner(ish)? I'm just wondering if its something thats learnable from a book by myself or is a classroom setting better?

 

[Datsmart]

Sorry, I got mixed up on what you wanted to move when you changed from Column B to Column A.
This code should do better.

Sub FooRng2()
Dim Rng As Range
Dim c As Range
Dim i As Integer
Dim tv As String

Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row)
For i = 1 To 3
    If i = 1 Then tv = "Incident Description:": os = 8  'os is Column OffSet value
    If i = 2 Then tv = "Incident Follow-up:": os = 9
    If i = 3 Then tv = "Corrective Actions:": os = 10
    For Each c In Rng
        If c.Value = tv Then
        c.Offset(0, 1).Cut c.Offset(-1, os)
        c.EntireRow.Delete
        End If
    Next c
Next i
End Sub