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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry.. make that
For i = 4 To 76 'C to BX
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)"
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
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
ASKER
Thanks mgh_mgharish
I just needed to change the C4 so that works perfect!
I just needed to change the C4 so that works perfect!
[C1:BX1].Formula = "=VLOOKUP(C4, Data!$G$3:$EA$87, 34, FALSE)"
Kevin