How do I cut and paste using a macro....

[Ian Mac]

Hello.

I'm having trouble creating a macro that will let me cut postcodes
from one cell and paste into another cell. I would appreciate if
anybody can help me.

What I have, is a large excel spreadsheet with a couple of thousand
names and addresses. The address fields start in column B through to
G. Each line of the address should be in a separate cell within each
row, but the postcodes have all ended up in the same cell as the last
line of the address. I need to cut the postcodes out of these cells
and paste them into column H which is empty.

The addresses are different lengths, leaving the postcodes in
different columns (usually columns D & E). The postcodes are not all
the same size, but the majority of them are 8 characters long. The
postcode is basically the last 8 characters in each cell.

What I want to be able to do, is to select the cell containing the
postcode, then run a macro that will remove it from the cell and paste
it into the H column on the same row.

I need the macro to work within any column and still cut and paste the
last eight characters to the H column.

Once I have this working I think I'll be able to work out how to
modify the macro to do the same for 7 or even 6 character long
postcodes.

Any help is much appreciated.

Ian.

 

[Gregg Roberts]

Couple things.

Cutting and pasting are slower than simply setting
values. Not sure if speed is a consideration but it's
smart to at least consider this before you start.

What you really need to do is get the postal code (PC),
reset the value of the address to remove the PC, and then
set another cell value equal to the PC.

The code below assumes there's a space between whatever
precedes the postal code, and no spaces IN the postal
code. The InstrRev function returns the position of
the " " character from the left end of the cell value,
but the "Rev" part makes it search from the right:

PCLength = Len(AddressCell) - InstrRev(AddressCell, " ") -
1
PC = Right(AddressCell, PCLength)
AddressCell = Left(AddressCell, (Len(AddressCell) -
PCLength - 1))
PCCell = PC

If there is no consistent delimiter before ther PC,
you'll have to find another way of determining where the
PC starts. The IsNumeric function would work if there's
no hyphen in the PC. You would have to work backwards,
testing each character from the right end of the address,
until you find a non-numeric character.

HTH,

Gregg