Can someone try this and let me know if it works for them - Thanks

Posted by Brian P on June 15, 2001 11:54 AM

I posted this earlier but didn't get a response. Can someone try this and let me know if it works for you.
I'm trying to get a data import into a database type format and can't get this piece of the code to work.

On a blank sheet put CCG in cell "e1" and put anything in "f1" and "g1". Then copy e1:g1 to e4, e8 and e12.
Now try the following. Does it work for you?

Thanks

Sub test()

Range("A1:C1").Select
Selection.FormulaArray = _
"=IF(LEFT(RC[4],3)=""CCG"",TRANSPOSE(RC[4]:R[2]C[4]),"""")"
Selection.AutoFill Destination:=Range("A1:C20")

Columns("A:C").Copy
Columns("A:C").PasteSpecial Paste:=xlValues

Columns("G:G").SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

End Sub

Posted by Russell on June 15, 2001 1:18 PM

What problem are you having?

Posted by Brian P on June 15, 2001 1:26 PM

Re: What problem are you having?

The last line won't execute - it makes me shut down excel. I have used the last two lines of code Columns("G:G").SpecialCells(xlCellTypeBlanks).Select

many times with no problem, but for some reason it won't work with the lines before it. I haven't a clue.

Posted by Russell on June 15, 2001 1:49 PM

Re: What problem are you having?

I get the same result. Maybe try looping through the cells in column G instead...

The last line won't execute - it makes me shut down excel. I have used the last two lines of code : Columns("G:G").SpecialCells(xlCellTypeBlanks).Select

Posted by Brian P on June 15, 2001 3:00 PM

Thanks Russell...

I can loop or sort, but its much slower. Thanks for your help

I get the same result. Maybe try looping through the cells in column G instead...