1

Having an issue trying to get the column letter from a column number. When the column number is 36 it comes back as blank, when it is 35 or 37 or any other number it comes back with the results. Here is the code:

ActiveCell.Select
L5Num = ActiveCell.Column
L5 = Split(Columns(L5Num).Address, "$")(1)
BigBen
  • 46,229
  • 7
  • 24
  • 40
Christopher Harris
  • 21
  • 3
  • 3
    IMO, using the column letter is a bad idea. Just use the column index with `Cells`, for example. – BigBen Sep 15 '21 at 14:22
  • 3
    ^^^^ You would use `Worksheet("blahblah").Cells(RowNum,ColumnNum)` then you do not need to parse a string. – Scott Craner Sep 15 '21 at 14:24
  • `L5 = Split(ActiveCell.Address, "$")(1)`. Your current code returns `AJ:`. But.... don't do this. It's counterproductive and unnecessary. – BigBen Sep 15 '21 at 14:25
  • Why do you want the letter? As written above, your code returns `AJ` for column 36, which is the correct designation. – Ron Rosenfeld Sep 15 '21 at 14:34
  • You can safely drop the line `ActiveCell.Select`. Using `Select` is bad practice at the best of times, but in this case all you're doing is selecting the cell that's already selected. – jsheeran Sep 15 '21 at 15:03
  • I agree with other commenters that you should avoid using column letters for any purpose other than for display. But, presuming you have good reason for wanting the column letter, you could get it direct from the address of the active cell. You don't need to get the column number. `L5 = Split(ActiveCell.Address, "$")(1)` – JohnRC Sep 15 '21 at 15:50
  • As to OP's actual question, it seems unlikely that the behaviour observed is due to the code that is posted - there's no way the result would be blank just for one specific column. I suspect there is other code not shown in the question that is modifying the result in some way. – JohnRC Sep 15 '21 at 15:54
  • Some hints: a) Every `ActiveCell` is already a *selected* one, you don't need to do it twice. b) If you want to get the column letter string without `":"` you need to change your code to `l5 = Split(Columns(l5num).Address, "$")(2)` or modify it to `l5 = Split(Cells(, l5num).Address, "$")(1)`. c) just an assumption: as you seem to rely on `ActiveCell` instead of fully qualifying a worksheet's range reference it might be that your active cell isn't located there where you think it is within your current code. @ChristopherHarris – T.M. Sep 15 '21 at 16:55
  • Does this answer your question? [Function to convert column number to letter?](https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter) – Storax Sep 16 '21 at 12:09

0 Answers0