PDA

View Full Version : Solved: Another 2010 problem



Blackie50
06-07-2011, 03:49 AM
Hi

I am running the following code (after creating a pivot table) in 2003 (and again in 2010)

For Each cel1 In Range(Cells(5, 1), Cells(Rows.Count, 1).End(xlUp))
If cel1.Offset(, 1).Value > 0 Then

cel1.Offset(, 4) = Split(cel1, " ")(0)
cel1.Offset(, 7) = Split(cel1, " ")(1)

This takes row 5 col A entry (e.g) CMFC and splits it to

CMF in row 5 col E
C in row 5 col H

works perfectly in 2003 but get the following in 2010

puts CMFC in row 5 col E (ie does not split)

and then bugs out next row

runtime error '9'
subscript out of range

Any ideas why it won't work in 2010?

many thanks
Jon

Kenneth Hobs
06-07-2011, 05:00 AM
Please attach a short example file to test.

Blackie50
06-07-2011, 06:05 AM
Hi There,

Can't attach as workbook to big

Is there another way to extract the first 3 characters (placing at the different location) and then character 4?

thanks again

Kenneth Hobs
06-07-2011, 06:08 AM
Oh, I see now. Why don't you use Left() and Right()? You should also use Len(cell) to make sure that it is 4 before using the Left() and Right().

Blackie50
06-07-2011, 07:14 AM
No need to use len as always 4 letters in that column

Left & Right work fine

thanks again for your help

regards

Jon