VBA moving cell contents

[hopelessliar]

Hi all,
I'm working on a sheet in excel and I've come up against a problem I can't seem to solve, any help would be gratefully received...

Essentially, the sheet is 2 columns and what I want to do is move data from one column to another based on the value of the cell. I think I'm ok with scanning the first column and selecting the cells to move but what I can't work out is how to find the end of the second column in order to paste the values there. In case that isn't well explained, I'll try and show an example:

col1 col2
1 -1
2 -4
3 -2
-5
6
-7

So in this example I want to move the negative numbers from column 1 to column 2 and paste them after the existing values (ie after -2 in this case). Can anybody help?


Thanks

 

[hopelessliar]

I've come up with the answer, it's actually really simple but for some reason my brain was refusing to see the easy answer! In case it helps anybody, this is how it's done:

I used a variable called length

length = Application.WorksheetFunction.CountA(Range("A:A"))

so that gives you the number of rows being used and it can be referenced like this in order to paste things on the end:

Cell.Copy Destination:=Range("A" & num + 1)

There may well be a better way to do this, but at least this works!

 

[SkipVought]

Hi,

As a general rule, I advise against cut 'n' paste operations.

You could use a simple formula to create two new columns: POS and NEG
[tt]
POS column
=if(a1>0,a1,0)
NEG column
=if(a1<0,a1,0)
[/tt]
Source data, IMHO, ought to remain source data.

Another alternative is to us MS Query. faq68-5829.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]