Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. A B 1. Original Negative 2. 3.72545 3. 3.4584 4. 3.1071 5. -0.460399 6. -0.803222 7. -1.12457 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and move values <0
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 On Sep 26, 9:13*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. * * * * *A * * * * * * * * *B 1. * Original * * Negative 2. * 3.72545 * * 3. * 3.4584 * * 4. * *3.1071 * * 5. -0.460399 * * 6. -0.803222 * * 7. -1.12457 * * |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and move values <0
may be this ??
in col B put this forumula =IF(B20,"",B2) select the col A & then go format | conditinal format | choose formula is :=B2=C2 | format | font tab | choose the white color | ok | ok On Sep 26, 4:13*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. * * * * *A * * * * * * * * *B 1. * Original * * Negative 2. * 3.72545 * * 3. * 3.4584 * * 4. * *3.1071 * * 5. -0.460399 * * 6. -0.803222 * * 7. -1.12457 * * |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and move values <0
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 -- Ian -- "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. A B 1. Original Negative 2. 3.72545 3. 3.4584 4. 3.1071 5. -0.460399 6. -0.803222 7. -1.12457 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and move values <0
sorry !!!
in col B put this forumula =IF(A20,"",A2) select the col A & then go format | conditinal format | choose formula is :=B2=C2 | format | font tab | choose the white color | ok | ok On Sep 26, 4:13*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. * * * * *A * * * * * * * * *B 1. * Original * * Negative 2. * 3.72545 * * 3. * 3.4584 * * 4. * *3.1071 * * 5. -0.460399 * * 6. -0.803222 * * 7. -1.12457 * * |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and move values <0
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" wrote: 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 -- Ian -- "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. A B 1. Original Negative 2. 3.72545 3. 3.4584 4. 3.1071 5. -0.460399 6. -0.803222 7. -1.12457 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find and move values <0
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 -- Ian -- "Sojo" wrote in message ... 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" wrote: 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 -- Ian -- "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. A B 1. Original Negative 2. 3.72545 3. 3.4584 4. 3.1071 5. -0.460399 6. -0.803222 7. -1.12457 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code - Find & Move | Excel Discussion (Misc queries) | |||
Find text and move | Excel Discussion (Misc queries) | |||
find text and move it | Excel Discussion (Misc queries) | |||
Find and Move Data | Excel Discussion (Misc queries) | |||
Macro - Find a value and then move down | Excel Discussion (Misc queries) |