Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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. "Peo Sjoblom" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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 On Sep 26, 11:58*pm, Sojo wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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 "Pete_UK" wrote: 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 On Sep 26, 11:58 pm, Sojo wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
NG = News Group
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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" wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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").S ort.SortFields.Clear ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort.SortFields.Add Key:=Range("YourColumn"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort .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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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 "NoodNutt" wrote 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").S ort.SortFields.Clear ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort.SortFields.Add Key:=Range("YourColumn"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort .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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
These are all one line
ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort.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 On Sun, 28 Sep 2008 14:31:01 -0700, Sojo wrote: ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort.SortFields.Add Key:=Range("YourColumn"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Repost of find and move values<0
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. "Gord Dibben" wrote: These are all one line ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort.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 On Sun, 28 Sep 2008 14:31:01 -0700, Sojo wrote: ActiveWorkbook.Worksheets("YourOthrerSheetName").S ort.SortFields.Add Key:=Range("YourColumn"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and move values <0 | Excel Worksheet Functions | |||
VBA Code - Find & Move | Excel Discussion (Misc queries) | |||
Find text and move | Excel Discussion (Misc queries) | |||
repost from general area ref values with column to left HELP | Excel Worksheet Functions | |||
Macro - Find a value and then move down | Excel Discussion (Misc queries) |