Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: VBA : increment a variable?

  1. #1
    Join Date
    22nd March 2003
    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.

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    7th February 2003
    Kewarra Beach, Cairns - TNQ

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Increment Variable Control Name
    By dodger7 in forum Excel General
    Replies: 1
    Last Post: August 23rd, 2008, 02:40
  2. Increment Column Letter Variable
    By lifeson99 in forum Excel General
    Replies: 1
    Last Post: March 17th, 2008, 00:51
  3. Replies: 10
    Last Post: June 6th, 2007, 22:21
  4. Increment Cell By One Row In Variable
    By bcreech in forum Excel General
    Replies: 3
    Last Post: December 15th, 2006, 05:22
  5. Replies: 3
    Last Post: August 12th, 2006, 12:50


Posting Permissions

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