IF statement
Posted by John Pollard on April 11, 2001 6:53 AM
I am just getting started with Excel and need help on two macro functions; The first is where I have a column with a text cell followed by various cells with the number 1, followed by a text cell followed by various cells with the number 1, etc. I would like to replace each of the number 1's with the text cell above it for the entire column. It would read copy, drop down one cell, if this cell = 1, paste, else copy, drop down one row, if cell = 1, paste, else etc.
For my second problem, I have sorted a large spreadsheet by a column and i would like to delete all rows where the cell in the sorted colum does not equal a specified value.
Thanks for yourn help.
John
Posted by John Ballance on April 11, 2001 6:56 AM
Text1
1
1
1
Text2
1
1
Text3
I would like to replace each of the number 1's wi1th the text cell above it for the entire column. It would read copy, drop down one cell, if this cell = 1, paste, else copy, drop down one row, if cell = 1, paste, else etc.
Posted by Big Bob on April 11, 2001 7:30 AM
For the answer to your second problem I would suggest reading about the AUTOFILTER component of Excel.You would use this to only show the rows meeting a specified condition then using paste-special-visible cells only to a new worksheet.
Posted by Another John on April 11, 2001 8:34 AM
For your first problem, try this.
Select the column or columns you want to act on.
Edit>GoTo>Special and choose 'Constants'. Uncheck 'Text', 'Logicals', and 'Errors', but leave numbers checked (assuming your number 1s are constants). Click 'OK'.
This leaves you with all of your 1s selected, but none of the text entries.
Now type '=' followed by the up arrow, followed by <Ctrl> and <Enter> together. This applies the formula 'equals the cell above' to all the selected cells.
With a bit of luck, that does it. If you want to do it over and over, record as a macro.
John.
