Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10460
  • Last Modified:

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!
0
somorain
Asked:
somorain
1 Solution
 
Harisha M GCommented:
Hi somorain,

do you want only C4 to change or G3 EA etc. also?

Try this:

For i = 1 To 76
ActiveCell.Formula = "=VLOOKUP(" & Split(Columns(i).Cells(1, 1).Address(1), "$")(1) & "4, Data!G3:EA87, 34, FALSE)"
Next

---
Harish
0
 
zorvek (Kevin Jones)ConsultantCommented:
You don't need to iterate:

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

Kevin
0
 
Harisha M GCommented:
Sorry.. make that

For i = 4 To 76  'C to BX
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Harisha M GCommented:
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)"
0
 
zorvek (Kevin Jones)ConsultantCommented:
Harish! Are you married yet?
0
 
hiteshgoldeneyeCommented:
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

0
 
somorainAuthor Commented:
Thanks mgh_mgharish

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now