Data Type problem with Array 3

[rocknrisk]

Hi all,

I have a data type issue here with my code (according to the compiler) at line 7 (For colID = ...).

Sub rcbTrim()
Dim colIDArray
Dim colID
colIDArray = Array("B", "E", "G", "I", _
"K", "M", "O", "Q", "AG", "AI", _
"AK", "AM", "AO", "AQ&quot
For colID = colIDArray(0) To colIDArray(13)
Columns(colID & ":" & colID).Insert Shift:=xlToRight
Range(colID & "2&quot.FormulaR1C1 = "=TRIM(RC[-1])"
Range(colID & "2&quot.AutoFill Destination:=Range _
(colID & "2:" & colID & "20001&quot, Type:=xlFillDefault
Next colID
End Sub

Please can someone tell me what I'm doing wrong.

Thank you in advance,
Clinton

"The important thing is not to stop questioning." - Albert Einstein

 

[robcarr]

slightly diff way of doing this, try this

Sub rcbTrim()
Dim colIDArray
Dim colID

colIDArray = Array("B", "E", "G", "I", _
"K", "M", "O", "Q", "AG", "AI", _
"AK", "AM", "AO", "AQ&quot
For Each colID In colIDArray
Columns(colID & ":" & colID).Insert Shift:=xlToRight
Range(colID & "2&quot.FormulaR1C1 = "=TRIM(RC[-1])"
Range(colID & "2&quot.AutoFill Destination:=Range _
(colID & "2:" & colID & "20001&quot, Type:=xlFillDefault

Next colID


End Sub

Thanks Rob.

 

[SkipVought]

Clinton,

Just think about it...

Your array of column references is ("B", "E", "G", "I", _
"K", "M", "O", "Q", "AG", "AI", _
"AK", "AM", "AO", "AQ&quot

What happens to columns ("E", "G", "I", _
"K", "M", "O", "Q", "AG", "AI", _
"AK", "AM", "AO", "AQ&quot when you go to column B and insert a column shifting EVERYTHING to the right???

Try looping thru your array in REVERSE.

I am assuming that ("B", "E", "G", "I", _
"K", "M", "O", "Q", "AG", "AI", _
"AK", "AM", "AO", "AQ&quot already has stuff.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi rocknrisk,

The type mismatch is because the For A = B To C construct requires numbers and you are giving it strings.

You could do ..

For x = LBound(colIDArray) to UBound(colIDArray)
colID = colIDArray(x)
:
:

BUT ..

It isn't the only issue you have ..

I see Skip has beaten me to it, so just the code ..

For x = UBound(colIDArray) to LBound(colIDArray) Step -1
colID = colIDArray(x)
:
:

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[rocknrisk]

Hi all,

Thank you.

Rob - It works. Cheers CL Macroman - how's the little one.

Skip & Tony - Thank you... I have alloted for that but thinking about rewriting in a better format. I'm currently rewriting old macros that are very long because they were recorded. Will keep the reverse practice in mind.

Thank you again all. Your help is much appreciated.
Clinton

"The important thing is not to stop questioning." - Albert Einstein

 

[robcarr]

rocknrisk,

have 2 now

Thanks Rob.

 

[rocknrisk]

Hey Rob,

Congratulations mate.

Email me at clinton.edwards@edfenergy.com. I'll reply. I don't remember your email address.

Thanks for the help.
Clinton

"The important thing is not to stop questioning." - Albert Einstein