Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert a column number to a column letter

1. Member
Join Date
20th October 2005
Location
Posts
69

## Convert a column number to a column letter

Hello everyone,

I ned to convert a column letter to a column number for the following line of code in my VBA application:

VB:
```strAverageBaseFormula = "=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":K" & Trim(Str(lngFirstRowOfIncrement + 1)) & ")"

```
I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in teh middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?

Thanks!
Bryan
Last edited by bdonkersgoed; November 22nd, 2005 at 01:14.

Excel Video Tutorials / Excel Dashboards Reports

2. Member
Join Date
20th October 2005
Location
Posts
69

## Re: Convert a column number to a column letter

Someone must know this?

Basically, how do I convert "11" to "K"??

Thanks,
Bryan
Last edited by bdonkersgoed; November 22nd, 2005 at 01:50.

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
22nd November 2005
Posts
1

## Re: Convert a column number to a column letter

VB:
```If MyColumn > 26 Then
MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65)
Else
MyColumnLetter = Chr(MyColumn + 64)
End If

```

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Convert a column number to a column letter

Bryan

Why not just use R1C1 notation for the formula?

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Convert a column number to a column letter

One way,
VB:
```=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":" & _
Left(Cells(1, 11).Address(False, False), Len(Cells(1, 11).Address(False, False)) - 1) & Trim(Str(lngFirstRowOfIncrement + 1)) & ")"

```

6. Member
Join Date
20th October 2005
Location
Posts
69

## Re: Convert a column number to a column letter

Originally Posted by tonyrosen
VB:
```If MyColumn > 26 Then
MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65)
Else
MyColumnLetter = Chr(MyColumn + 64)
End If

```
Hehehe . . . thanks! I actually just wrote code almost identical to this, and was about to post it :P I thought there should be an easier way though.

Thanks again!
Bryan

Excel Video Tutorials / Excel Dashboards Reports

7. Member
Join Date
20th October 2005
Location
Posts
69

## Re: Convert a column number to a column letter

Originally Posted by norie
Bryan

Why not just use R1C1 notation for the formula?
Hmmmm . . . I guess that would have been the logical choice. I never use that notation though, and the spreadsheet that I am creating is for people who would not understand R1C1 at all.

Thanks though!
Bryan

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Convert a column number to a column letter

Bryan

The formula would appear in whatever notation the workbook was set to show.

Excel Video Tutorials / Excel Dashboards Reports

9. Member
Join Date
20th October 2005
Location
Posts
69

## Re: Convert a column number to a column letter

Originally Posted by norie
Bryan

The formula would appear in whatever notation the workbook was set to show.
Oh, I didn't knwo that . . . thanks

Too bad I just wrote a bunch of useless code then

Regards,
Bryan

Excel Video Tutorials / Excel Dashboards Reports

10. I agreed to these rules
Join Date
23rd December 2011
Posts
1

## Re: Convert a column number to a column letter

her is the easyest way, witch wil work with any number big

VB:
```Public Function ColLett(Col As Integer) As String

If Col > 26 Then
ColLett = ColLett((Col - (Col Mod 26)) / 26) + Chr(Col Mod 26 + 64)
Else
ColLett = Chr(Col + 64)
End If

End Function

```

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno