VBA Question: Move cell contents if...
 

Need some VBA help to move contents of column G to column C if column C="",
and delete contents column G after move.

Logically:

IF C="" (move G to C then delete G),(do nothing)

Can you help with a macro please?

Thanks,

Scott

VBA Question: Move cell contents if...
 

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
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



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Scott Wagner wrote:

Need some VBA help to move contents of column G to column C if column C="",
and delete contents column G after move.

Logically:

IF C="" (move G to C then delete G),(do nothing)

Can you help with a macro please?

Thanks,

Scott

--

Dave Peterson

VBA Question: Move cell contents if...
 

Try this, works for me

lastrow = Worksheets("Sheet1").UsedRange.Row + Worksheets("Sheet1").UsedRange.
Rows.Count - 1

For a = 1 To lastrow
If Sheet1.Cells(a, 3).Value = "" Then
Range("C" & a).Value = Range("G" & a).Value
Range("G" & a).ClearContents
End If
Next a

Regards



--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

VBA Question: Move cell contents if...
 

Dave,

Can the "sheet1" be changed to the active worksheet instead? How would I do
that?

Thanks for you help.

Sincerely,

Scott

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
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



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Scott Wagner wrote:

Need some VBA help to move contents of column G to column C if column C="",
and delete contents column G after move.

Logically:

IF C="" (move G to C then delete G),(do nothing)

Can you help with a macro please?

Thanks,

Scott

--

Dave Peterson

VBA Question: Move cell contents if...
 

Change this:
With Worksheets("sheet1")
to
With Activesheet



Scott Wagner wrote:

Dave,

Can the "sheet1" be changed to the active worksheet instead? How would I do
that?

Thanks for you help.

Sincerely,

Scott

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
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



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Scott Wagner wrote:

Need some VBA help to move contents of column G to column C if column C="",
and delete contents column G after move.

Logically:

IF C="" (move G to C then delete G),(do nothing)

Can you help with a macro please?

Thanks,

Scott

--

Dave Peterson


--

Dave Peterson

VBA Question: Move cell contents if...
 

One last question on this topic. How can I change the following from (is
="") to (is not = "")

If .Cells(myCell.Row, "C").Value = ""

Thanks,

Scott






"Dave Peterson" wrote:

Change this:
With Worksheets("sheet1")
to
With Activesheet



Scott Wagner wrote:

Dave,

Can the "sheet1" be changed to the active worksheet instead? How would I do
that?

Thanks for you help.

Sincerely,

Scott

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
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



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Scott Wagner wrote:

Need some VBA help to move contents of column G to column C if column C="",
and delete contents column G after move.

Logically:

IF C="" (move G to C then delete G),(do nothing)

Can you help with a macro please?

Thanks,

Scott

--

Dave Peterson


--

Dave Peterson

VBA Question: Move cell contents if...
 

If .Cells(myCell.Row, "C").Value = ""
becomes
If .Cells(myCell.Row, "C").Value < ""



Scott Wagner wrote:

One last question on this topic. How can I change the following from (is
="") to (is not = "")

If .Cells(myCell.Row, "C").Value = ""

Thanks,

Scott

"Dave Peterson" wrote:

Change this:
With Worksheets("sheet1")
to
With Activesheet



Scott Wagner wrote:

Dave,

Can the "sheet1" be changed to the active worksheet instead? How would I do
that?

Thanks for you help.

Sincerely,

Scott

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
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



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Scott Wagner wrote:

Need some VBA help to move contents of column G to column C if column C="",
and delete contents column G after move.

Logically:

IF C="" (move G to C then delete G),(do nothing)

Can you help with a macro please?

Thanks,

Scott

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson