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

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,

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
>

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

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
>

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

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

prec_tec@yahoo.com

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