Retrieving the Column name (e.g. BT) from a numeric

[niall]

Hi,

I am importing a csv into Excel (Excel 2000) which will have a
varying number of fields in the imported file.

I can loop over a particular row to get the first blank cell in
this row.

As an example, the test file I am using comes back with the first
blank cell as the numeric 72. By looking at the spreadsheet (or by
dividing by 26 plus using the remainder), i can see that this
translates to cell reference BT.

I looked around in the VB help but couldn't find a property (like
say .columname) which would allow me to use this for clearing contents
and formatting the cells within the 'Active' worksheet.

Here is my code to find the last valid column :

For inti = 3 To 256
y = Worksheets("sheet1").Cells(4, inti).Value
If y = "" Then
intlastcol = inti
inti = 257
End If
Next inti

Anyone know if there is such a beast out there ?

Thanks in advance


Niall Heelan

 

[Jason]

Hi Niall,

I'm not sure if this will help you or not, but when I'm doing this type of
thing, I usually use .end(xltoRight).offset(0,1) to find the first empty

You could set up a variable for the empty cell
Dim emptyCell as range

then do this:

set emptyCell = range("A4").end(xltoRight).offset(0,1)

That way you would have a reference to the first empty column and from there
you could hop around wherever you want.

Like I said, I don't know if it will apply to your situation.

Jason

 

[Dave Peterson]

VBA is pretty nice.

You don't need to use letters to refer to a column.

Cells(1,"A")
and
Cells(1,1)
both refer to A1.

dim Inti as long
dim IntLastCol as long
intLastCol = -1
For inti = 3 To 256
y = Worksheets("sheet1").Cells(4, inti).Value
If y = "" Then
intlastcol = inti
exit for
End If
Next inti
if intlastcol = -1 then
'no blanks!
else
.cells(1, intlastcol).entirecolumn.clearcontents
end if

But if you want, here's a way to get the letter/string:

dim ColLetter as string
dim intlastcol as long
intlastcol = 72 'or whatever you find

'this will return something like BT1
colletter = activesheet.cells(1,inlastcol).address(0,0)
'and strip the 1 from the string
colletter = left(colleter, len(colletter)-1)
msgbox colletter

 

[niall]

Hi Dave,


Thanks for the reply (you too Jason), that's what I was looking
for.


Regards,


Niall Heelan