VBA Question: Move cell contents if...

[Guest]

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]

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

 

[Crowbar via OfficeKB.com]

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

(e-mail address removed)

 

[Guest]

Dave,

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

Thanks for you help.

Sincerely,

Scott

 

[Dave Peterson]

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

 

[Guest]

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]

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

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