In the following piece of code, as you can see I select Column E and
clear the contents for any zero data in the column. What I then need
to do is take any NON-zero data, multiply it by -1 (make it negative,
that is) and then move it over one column (so that it no longe exists
in its original column). How do I do this dynamically? you can see
below I am stuck at the "Else" part.

Thanks a lot!

Columns("E").Select
For Each Cell In Selection
If Cell.Value = 0 Then
Cell.ClearContents
Else: Cell.Cut?????????
End If
Next Cell

I need to move it one cell over to the LEFT for each occurence. Sorry
for the ommission.

On Apr 28, 7:43 pm, Zarlot531 <x...@hotmail.com> wrote:
> In the following piece of code, as you can see I select Column E and
> clear the contents for any zero data in the column. What I then need
> to do is take any NON-zero data, multiply it by -1 (make it negative,
> that is) and then move it over one column (so that it no longe exists
> in its original column). How do I do this dynamically? you can see
> below I am stuck at the "Else" part.
>
> Thanks a lot!
>
> Columns("E").Select
> For Each Cell In Selection
> If Cell.Value = 0 Then
> Cell.ClearContents
> Else: Cell.Cut?????????
> End If
> Next Cell

this may work for you (untested)

Sub test()
Dim cell As Range
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
lastrow = Cells(Rows.Count, "E").End(xlUp).Row

For Each cell In ws.Range("E1:E" & lastrow)
If cell.Value > 0 Then
cell.Offset(0, -1).Value = cell.Value
cell.ClearContents
Else
cell.ClearContents
End If
Next
End Sub

--


Gary


"Zarlot531" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In the following piece of code, as you can see I select Column E and
> clear the contents for any zero data in the column. What I then need
> to do is take any NON-zero data, multiply it by -1 (make it negative,
> that is) and then move it over one column (so that it no longe exists
> in its original column). How do I do this dynamically? you can see
> below I am stuck at the "Else" part.
>
> Thanks a lot!
>
> Columns("E").Select
> For Each Cell In Selection
> If Cell.Value = 0 Then
> Cell.ClearContents
> Else: Cell.Cut?????????
> End If
> Next Cell
>

Thanks!!

With your help, this is what worked (see bottom of code):

Option Explicit

Sub DelRw()
Dim lstRw
Dim i
Dim x
Dim CalcMode
Dim Cell As Range
Dim lastrow As Long
Dim ws As Worksheet



With Application
CalcMode = .Calculation
..Calculation = xlCalculationManual
..ScreenUpdating = False
End With
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(20, 1), Array(34, 1),
Array(42, 1), Array(52, 1), _
Array(54, 1), Array(66, 1), Array(76, 1), Array(86, 1),
Array(108, 1)), _
TrailingMinusNumbers:=True
Columns("C:G").Select
Selection.Delete Shift:=xlToLeft
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lstRw To 1 Step -1
x = Cells(i, 3).Value
If Left(x, 4) <> "2745" Then
Cells(i, 3).EntireRow.Delete
End If
Next
Columns("E").Select
For Each Cell In Selection
If Cell.Value = 0 Then
Cell.ClearContents
Else: Cell.Offset(0, -1).Value = Cell.Value * -1
Cell.ClearContents
End If
Next Cell
With Application
..ScreenUpdating = True
..Calculation = CalcMode

End With

End Sub


On Apr 28, 8:09 pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> this may work for you (untested)
>
> Sub test()
> Dim cell As Range
> Dim lastrow As Long
> Dim ws As Worksheet
> Set ws = Worksheets("Sheet1")
> lastrow = Cells(Rows.Count, "E").End(xlUp).Row
>
> For Each cell In ws.Range("E1:E" & lastrow)
> If cell.Value > 0 Then
> cell.Offset(0, -1).Value = cell.Value
> cell.ClearContents
> Else
> cell.ClearContents
> End If
> Next
> End Sub
>
> --
>
> Gary
>
> "Zarlot531" <x...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > In the following piece of code, as you can see I select Column E and
> > clear the contents for any zero data in the column. What I then need
> > to do is take any NON-zero data, multiply it by -1 (make it negative,
> > that is) and then move it over one column (so that it no longe exists
> > in its original column). How do I do this dynamically? you can see
> > below I am stuck at the "Else" part.
>
> > Thanks a lot!
>
> > Columns("E").Select
> > For Each Cell In Selection
> > If Cell.Value = 0 Then
> > Cell.ClearContents
> > Else: Cell.Cut?????????
> > End If
> > Next Cell- Hide quoted text -
>
> - Show quoted text -