Repost of 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.


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

I have several excel docs each with only 1 worksheet. My columns run from A
to DO (this will be constant) and rows go to 2280 (this will not be
constant). Because of this I would prefer a macro function instead of a
formula.

IanC's gave me the following macro.

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

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.

 

[Peo Sjoblom]

For future posts please stay in the original thread, by multiposting you
make
people irritable and you are decreasing the chance of getting an answer.

--


Regards,


Peo Sjoblom

 

[Sojo]

I apolozgize. I'm not used to posting and didn't understand why I didn't see
my reply at the beginning of the post. Won't happen again.

 

[Pete_UK]

What I don't understand is if you are using columns A to DO then how
can you use column B to house the negative values from column A? Give
a bit more detail about how your data is laid out.

Pete

 

[NoodNutt]

G'day Sojo

Just a few friendly pointers when using NG Forums:

1. Etiqette is important (A simple Hi, Thank you & a name at the end of
your request for help is always nice)
2. Patience is a vitue: You don't need to repost if there is a response
thread already running
3. Don't Cross Post your question over multiple NG's, it won't get you
too many favors as the practice is somewhat frowned upon by most
contributors.

As you may not be aware, contributors troll through hundreds of posts each
day, if everyone cross posted, it would be huge time waster to say the
least.

4. Provide feedback, A simple Thx if you got the result you were looking
for, if not, then provide additional information so we can try and rectify
it.

That said!!!!!!

Try this link
Read and follow the instruction that Ron has outlined, it works well.

http://www.rondebruin.nl/copy2.htm

If you still have issues, create a new message clearly defining what the
problem is.

HTH
Mark.

 

[Sojo]

Hello everyone:

Thanks for your patience and tips. I greatly appreciate it. Can I ask what
does NG mean?

Below is a sample of my data (hope it shows up the way I aligned it). The
original data had no blank columns. I recorded a macro to insert blanks next
to each data column. This is was in preparation to move all negative values
from "X Pos", "Y Pos", "Z Pos" to "X Neg", "Y Neg", "Z Neg", respectively.
This series of XX,YY, and ZZ repeats till column DO. With regards to rows,
all worksheets do not end on the same row.

In the meantime, I will take a look at the link Mark provided.

Thank you

Sojo

X Pos X Neg Y Pos Y Neg Z Pos Z neg
5.15129 -4.58382 44.6704
5.24563 -4.57916 45.0446
5.37303 -4.5259 45.3144
-4.18889 34.4863 -9.0217
-4.04946 34.3694 -9.38774

 

[NoodNutt]

NG = News Group

 

[Sojo]

Hi everyone:

I went through the link Mark provided and looked at few other links from
there. I realized that I'm probably complicating things by adding blank
columns in a separate macro. So, I'm not going to use that macro. My
original data has no blank columns. I can use one of the macros provided on
the link to copy and move the data. But I still don't know how to tell it to
look through each column and only move only the negative values over to a
newly created column adjacent to the source column.

Negative values are not together in consecutive rows. For instance column A
could have negative values in rows 1-10, positive values 11-144, negative
values 145-171 etc. The pattern for column B, C, D and so on are all
different.

I hope I'm not confusing the issue more.

Thanks,

Sojo.

 

[NoodNutt]

Sojo

I Use the same code to filter out specific data that my match criterion.

Modify to suit, e.g.

Sub Split_Data()

Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Start of <0 Extraction
Set SourceSheet = Sheets("YourSheetName")
Set rng = SourceSheet.Range("A1:B" & Rows.Count)
Set DestinationSheet = Sheets("YourOthrerSheetName")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="<0"

SourceSheet.AutoFilter.Range.Copy


With DestinationSheet.Range("A1")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With

Range("A1:B").Select
ActiveWorkbook.Worksheets("YourOthrerSheetName").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("YourOthrerSheetName").Sort.SortFields.Add
Key:=Range("YourColumn"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("YourOthrerSheetName").Sort
.SetRange Range("A1:B")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

SourceSheet.AutoFilterMode = False

Range("A1").Select

'End of <0 Extraction

'HTH
Mark

 

[Sojo]

Thanks Mark!

My worksheets names are the generic Sheet1, Sheet2, so I filled those in. I
didn't know what to put for "YourColumn" so I just put "D" to see what would
happen. When I tried to run the code I got an error that said" compile error
Expected: expression." It happens at part of the code below. Both lines
turn into red text and the := are highlighted. I get the same message for
the := following SortOn.

Key:=Range("YourColumn"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

What did I do wrong?

Sojo

 

[Gord Dibben]

These are all one line

ActiveWorkbook.Worksheets("YourOthrerSheetName").Sort.SortFields.Add _
Key:=Range("YourColumn"), _
SortOn:=xlSortOnValues, Order:=xlAscending,DataOption:=xlSortNormal

Note I have added one more line-continuation mark(_) to tie them together.


Gord Dibben MS Excel MVP

 

[Sojo]

Thanks for the tip. I modified the code and got beyond that error. However,
I ran into several other errors. I googled the errors and came up with a few
solutions .

Location: Range("A1:B"). Select.
Message: Run time error 1004. Range of obejct global failed.
Solution: changed code to Range("A1:B2285).Select

Location:ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
Message:Run time error 438 object doesn't support this property or method
Solution: Moved code from excel 2003 to 2007

Location:ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add
Key:=Range("D"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
Message: Run time error 1004. Range of obejct global failed.
Solution: Added 1 in front of D to make Key:=Range("D1")


Location: .Apply.
Message: run-time error '1004': "The sort reference is not valid. Make sure
it's within the data you want to sort, and the first Sort By box isn't the
same or blank.
Solution: None

So, I am not sure my solutions are 100% correct. And I have no idea how to
solve the last error.

Would appreciate any help.