Convert a dynamic column number into letter within a vba formula

**XLVBA** · 05-14-2015, 11:13 AM

In my workbook, users click a button which updates a worksheet. One of the things that I would like it to do is put in a number of formulas in the active row starting in column G and in every column until at least column BG.

This is the code that I was trying to use and it works in the sense that it puts the formula in all the correct rows and columns. The only problem that I have with it are the two places where I'm referencing "ActiveCell.Column" because it puts in the column number instead of the column letter. How can I change this so that it will reference the column letter?

Please Login or Register  to view this content.

Thank you!

**walruseggman** · 05-14-2015, 11:27 AM

There are a couple of ways.

First, you can convert the the column number to the ASCII letter number equivalent (by adding 64), then convert that ASCII number to a letter using Chr(). like so:

Please Login or Register  to view this content.

This works fine for single letter columns, but when you get up to column AA and beyond, you start having to account for the additional letter.

I think a more elegant approach is to split the address. If ActiveCell is A1, then ActiveCell.Address = "$A$1". You can split this string at the dollar signs, then grab the first position. This will grab the column letter regardless of how many letters it is. Like so:

Please Login or Register  to view this content.

Again, the second approach is much more robust, and since you mentioned column BG, i would say it's definitely the way to go.

**XLVBA** · 05-14-2015, 11:31 AM

The second approach is exactly what I needed. Thank you!

**walruseggman** · 05-14-2015, 11:33 AM

Glad to help. If you're satisfied with the solution, any reputation points (the * in the lower left of my posts) is appreciated.

**MrShorty** · 05-14-2015, 12:39 PM

I know this has been marked "solved". I would suggest, if only for educational purposes, another approach. One of the challenges, especially in VBA, of using A1 notation, is that formula strings change as they are copied around when you are using relative references. One of the advantages, especially in VBA, of R1C1 notation is that formula strings do not change as they are copied and moved around.

In your case, for example (assuming I follow correctly), the formula string in R1C1 notation would be "...AND(R2C>=RC5,R2C<=RC6)...". Note that, in this approach, there is no need to convert column numbers to letters. There would be no need to reference column number at all (except to specify which cell is receiving the formula).

If you are satisfied with the A1 solution you have, then ignore this post. I would suggest that, if you are going to be doing a lot of this kind of programming, that you will be well served to become familiar with R1C1 notation and the .FormulaR1C1 property.

**XLVBA** · 05-18-2015, 04:08 PM

Being protected, things don't really get moved around so the A1 solution works great. I'm sure there will be future workbooks where this will definitely be more of a concern so thank you very much for providing an alternative and the explanation. I've only started to familiarize myself with R1C1 so this was a great reminder to give that a shot. Thank you both!