VBA - converting a Column Number into Column letter

[Rasm]

I have over 2000 columns in my sheet - I am importing data from external binary random acces files - I bring these in as arrays of numbers - so how can I convert column 60 to a letter BH - column 186 to GD and so on.

 

[pgc01]

Hi

Try:

msgbox split(cells(1,[COLOR=red]60[/COLOR]).address,"$")(1)

 

[Norie]

Why do you need the column letter?

 

[Rasm]

PGC - Thanks

Norie - I have to set my range in order to convert the array onto my sheet. Or is there a better way. My range statement may look like this

ActiveSheet.Range(Astr).Value = Application.WorksheetFunction.Transpose(TemPvalues)

where Astr= "A30:B60" - this works fine

But I have tried to get this range to work

Astr="A30:A60,D30:D30" - This does not work - It would be sweet if I can pop all the values into the sheet in one go. Right now I use the TemPvalues arrauy to make sure they Columns are consequetive.

In both cases my array is diminsioned like this

redim TemPvalues(2,30)

This is just for an example - my real arrays are like up to 32K rows each 1050 columns - FYI - these are Infra-red absorbance spectra.

just realized that Astr is diminsioned as a string - But I dont thing that is the problem.

 

[Rasm]

PGC - that works perfect - and I assume it dont matter what alphabet you use.

 

[Norie]

Why not use Cells?

 

[Rasm]

Norie
I figured it is faster to fill the cells with the array values using a range - rather than using a loop - or can you fill cells directly from an array? - sorry for all these questions - but my VB experience is not from Excel - so I just learning the Excel side of the code - there sure is a million Excel specific commands. I do appreciate your questions - I am learning from them.

 

[Norie]

You can refer to cells/ranges like this:

Set rng = Cells(1,5).Resize(30)

That creates a reference to E1:E30.

 

[Rasm]

ahhh - OK -


1) Do you know if that is faster than using a range statement


2) Can you move an arrays say MyArray(5,30) - where I want the data to go into 5 columns (not consequtive columns so i.e. A, C, K, O & P ) - each columns having 30 observations added.

 

[Norie]

I honestly don't know, I would think it would make little difference, not anything that would be noticeable anyway.

Perhaps using Cells will be quicker because you don't need to use a function to get the column letter(s) and concatenation for the range?