find and move values <0

[Sojo]

I am writing a macro to automate several processes. I have the following
table where column A has positive and negative values and column B is blank.
I need a formula that will find all the negative values in column A and move
them to column B. In essence, it needs to say if number in column A < 0,
then cut it and paste it into column B.

A B
1. Original Negative
2. 3.72545
3. 3.4584
4. 3.1071
5. -0.460399
6. -0.803222
7. -1.12457

 

[MuppetMan]

Put into B2:
=if(A2<0,A2,"")

Copy down as far as you need. You can use a formula in one cell to
clear the contents of another cell.

However, I think you could use custom formatting to hide negative
values in column A.

Muppet Man

 

[muddan madhu]

may be this ??

in col B put this forumula =IF(B2>0,"",B2)

select the col A & then go format | conditinal format | choose formula
is :=B2=C2 | format | font tab | choose the white color | ok | ok

 

[IanC]

For r = 2 To 100
If Cells(r, 1).Value < 0 Then
Cells(r, 1).Copy
Cells(r, 2).PasteSpecial
Cells(r, 1).Value = ""
End If
Next

 

[muddan madhu]

sorry !!!


in col B put this forumula =IF(A2>0,"",A2)


select the col A & then go format | conditinal format | choose
formula
is :=B2=C2 | format | font tab | choose the white color | ok | ok

 

[Sojo]

Thanks for all the post. However, I did not add that my columns run from A
to DO (this will be constant) and row go to 2280 (this will not be contant).
So, I think IanC's idea of a macro would be easier.

I copied and pasted the macro as is into a VB module, but it didn't work. I
don't know much about code, so can't figure out what I did wrong.

Any thoughts?

 

[IanC]

You don't say whether anything at all happened, so I apologiose if I'm
telling you here what you already know.

You need to wrap it in a subroutine:

Sub MoveNegatives()
For r = 2 To 100
If Cells(r, 1).Value < 0 Then
Cells(r, 1).Copy
Cells(r, 2).PasteSpecial
Cells(r, 1).Value = ""
End If
Next
End Sub

You then need to run the macro. You can either do it in the VB editor (make
sure the flashing cursor in somewhere in the routine the click the Run
button or press F5), or you can do it from within Excel by going Tools >
Macro > Macros, highlighting the macro name and clicking "Run".

You said your data runs from columns A to DO. Do you mean you need to move
the negative numbers from column A to column DO? If so, change the number in
the PasteSpecial line from 2 to 119. You can find the column number of any
column using the columns keyword eg in any cell type =COLUMNS(DO1). The
returned number is the column number.

You said your data occupies a varying number of rows.

Assuming I am right in thinking you need to move the negative numbers from A
to DO, the data starts in row 2 and there are no blank rows in the data, the
following code should do.

Sub MoveNegatives()
' For row numbers 2 to last occupied row
For r = 2 To Cells.SpecialCells(xlCellTypeLastCell).Rows
' check value of cell in column A
If Cells(r, 1).Value < 0 Then
' if cell value is 0 or greater, jump to End If, otherwise step onto
next line
' copy contents of cell in column A
Cells(r, 1).Copy
' paste contents to cell in column DO
Cells(r, 119).PasteSpecial
' delete contents of cell in column A
Cells(r, 1).Value = ""
End If
' returns to the For line and increases r by 1
Next
End Sub