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 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" wrote in message
oups.com...
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" wrote in message

oups.com...



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 -