moving columns

[Dino]

Hello, there was a previously posted solution that worked great for moving
the value of one column to another column if certain criteria were met. The
code below analyzes column G, and if the cell in the same row in column C is
blank, then the value from column G is entered into that cell in column C.

Option Explicit
Sub Fillblanks()
Dim myRng As Range
Dim myCell As Range

With Activesheet
Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If .Cells(myCell.Row, "C").Value = "" Then
.Cells(myCell.Row, "C").Value = myCell.Value
myCell.Value = ""
End If
Next myCell
End With
End Sub

The result that I need is this:
Analyze column G and C the same way, and if the two values are equal to each
other, then move the values from column G and H ( I have two columns that are
associated with each other) to columns D and E. Would it also be possible to
analyze columns C and G, and move the values from G and H to wherever the
matching C values are, regardless if they are in the same row or not?

Thanks.
Dino

 

[JLGWhiz]

I think this is what you want:


Option Explicit
Sub matchCopy()
Dim myRng As Range
Dim myCell As Range

With Activesheet
Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))

For Each myCell In myRng.Cells
If .Cells(myCell.Row, "C").Value = myCell.Value Then
.Range("G" & myCell.Row & ":H" & myCell.Row).Copy _
.Range("D" & myCell.Row)
End If
Next myCell
End With
End Sub


Would it also be possible to analyze columns C and G,
and move the values from G and H to wherever the matching
C values are, regardless if they are in the same row or not?

Once the searched item is found, then data in location relative to
the found item cell can be cut, copied or otherwise manipulated to
anywhere in the workbook or other workbooks if need be. The only
condition is that if it is to be a repetitive operation, there must
be a consistent criteria that allows the data to be found and to
be located elsewhere. i.e. paste in next empty cell in a row or
next empty cell in a column, etc. Actually, data can be moved from
one place to any other place, but it is not practical to write
code for random movement or erratic movement.

 

[JLGWhiz]

Forgot to turn of the copy mode.

Option Explicit
Sub matchCopy()
Dim myRng As Range
Dim myCell As Range
With Activesheet
Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
For Each myCell In myRng
If .Cells(myCell.Row, "C").Value = myCell.Value Then
.Range("G" & myCell.Row & ":H" & myCell.Row).Copy _
.Range("D" & myCell.Row)
End If
Next myCell
Application.CutCopyMode = False
End With
End Sub

 

[Dino]

Thanks for the reply, I'll try it when I get back to work on Monday. My issue
is that I have thousands of rows of data that I have to compare and move in
this manner, was just hoping that I could automate the process instead of
cutting and pasting all these cells.

 

[JLGWhiz]

If you can describe the database layout (at least the portion you want to
work with) in terms of columns and rows, header rows, etc. And describe what
you do manually that you want to convert to automation, then someone who
monitors this group will try to help you. The code that I gave you copies
the data from cells in columns G and H to Columns D and E of the same row
where a match is found. It leaves the original data intact. If you want to
remove the data from the copied cells, simply change the word "Copy" in the
code to "Cut" and it will leave the cells blank in column G and H as it
pastes the data to D and E.

 

[Dino]

Thanks. The program gave me an error when I ran it. "Run-time error 424.
Object required". Debug mode highlighted the code in the If-End If loop.

 

[Patrick Molloy]

try changing

Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))

to

Set myRng = .Range( .Range("G1"), .Cells(.Rows.Count, "G").End(xlUp) )

 

[Dino]

Hmmm... same error, same highlighted code.

try changing

Set myRng = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))

to

Set myRng = .Range( .Range("G1"), .Cells(.Rows.Count, "G").End(xlUp) )

 

[Don Guillett]

Although I would have done it differently, I tested your code and it worked,
as written.

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Don Guillett]

Disregard. I tested the FIRST CODE. not the question. Offer to look still
stands.
Be sure to give before/after examples

 

[keiji kounoike]

the line below put the line-continuation character(_) into wrong place.
Delete _ and make it into one line or break at other point.

..Range("G" & myCell.Row & ":H" & myCell.Row).Copy _
.Range("D" & myCell.Row)

If you want to use _ , one example is like

..Range("G" & myCell.Row & ":H" & myCell.Row) _
.Copy Range("D" & myCell.Row)

Keiji

 

[keiji kounoike]

Sorry, I forgot to put . before Range("D" & myCell.Row)

..Range("G" & myCell.Row & ":H" & myCell.Row) _
.Copy Range("D" & myCell.Row)

should be

..Range("G" & myCell.Row & ":H" & myCell.Row) _
.Copy .Range("D" & myCell.Row)

Keiji

 

[Dino]

OK thanks. When I get back to work I will send you the example.