No announcement yet.

VBA : increment a variable?

  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA : increment a variable?

    I would like to loop through a range and then if the value of the cell to the right is "" (nothing) then I want to save that into a variable. After looping and saving then I would like to do another procedure that does what I want to do with those variables. Since I don't know how many there will be that meets that criteria, I would like to just use one variable name and be able to increment it (for example: myCell1, myCell2, myCell3,... ). Is it possible to increment a variable in that way? I experimented with trying myCell+1, and Dim i As Integer and myCell&i+1. Neither of these got me anywhere.

  • #2


    I may be misunderstanding what you are doing but when you assign a value to a single variable the previous value (if any) held by that variable is destroyed.

    If you are trying to create a list of cells that have blanks to the right you may want to use an array.

    eg: Highlight a range of cells. This code will check which of these has a blank cell next to them and paste a list of their addresses starting at E1

    Option Base 1
    Sub socha()
    Dim vaCells
    Dim x As Integer
    Dim i As Integer
    x = WorksheetFunction.CountBlank(Selection.Offset(0, 1))
    ReDim vaCells(1 To x)
    i = 1
    For Each cell In Selection
    If cell.Offset(0, 1).Value = "" Then
    vaCells(i) = cell.Address
    i = i + 1
    End If
    Range("e1").Resize(x, 1).Value = WorksheetFunction.Transpose(vaCells)
    End Sub

    edit: fixed up the array size
    Kind Regards
    Pesky Weasel
    "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
    Eagles may soar, but Weasels don't get sucked into jet engines.
    Templates and Calculators
    The Way of the Weasel
    Download Ivan & Colo's HTML Maker Here
    101 Excel Hacks - Great New Book
    Huge Savings on Excel Training