Link to home
Start Free TrialLog in
Avatar of somorain
somorain

asked on

Excel VBA - Loop through letters of alphabet

Hi.
I am looking for a way to loop through letters 'C' to 'BX' to populate cells in an excel spreadsheet with a formula.

This is my code:

    ActiveCell.Formula = "=VLOOKUP(C4, Data!G3:EA87, 34, FALSE)"
    ActiveCell.Offset(0, 1).Select

I want to create a loop that will increment C4 to D4, E4, F4.....all the way up to BX.
I'm sure there must be a simple way to do this?

Right now I'm manually changing the letter and executing the code over and over, which is very tedious!
ASKER CERTIFIED SOLUTION
Avatar of Harisha M G
Harisha M G
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You don't need to iterate:

[C1:BX1].Formula = "=VLOOKUP(C4, Data!$G$3:$EA$87, 34, FALSE)"

Kevin
Sorry.. make that

For i = 4 To 76  'C to BX
Kevin, what if the cells are different?

Also, your formula should be
[C1:BX1].Formula = "=VLOOKUP(C$4, Data!$G$3:$EA$87, 34, FALSE)"
Harish! Are you married yet?
For i=3 to 76
 ActiveCell.Formula = "=VLOOKUP(" & GetXLCol(i) & "4, Data!G3:EA87, 34, FALSE)"
    ActiveCell.Offset(0, 1).Select
Next

Privatee Function GetXLCol(Col As Integer) As String
      ' Col is the present column, not the number of cols
      Const A = 65    'ASCII value for capital A
      Dim iMults As Integer
      Dim sCol As String
      Dim iRemain As Integer
      ' THIS ALGORITHM ONLY WORKS UP TO ZZ. It fails on AAA
      If Col > 701 Then  
            GetXLCol = ""
            Exit Function
      End If
      If Col <= 25 Then
            sCol = Chr(A + Col)
      Else
            iRemain = Int((Col / 26)) - 1
            sCol = Chr(A + iRemain) & GetXLCol(Col _
                  Mod 26)
      End If
      GetXLCol = sCol
End Function

Avatar of somorain
somorain

ASKER

Thanks mgh_mgharish

I just needed to change the C4 so that works perfect!