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.
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
RegardsVB: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 Next Range("e1").Resize(x, 1).Value = WorksheetFunction.Transpose(vaCells) End Sub
edit: fixed up the array size
"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
There are currently 1 users browsing this thread. (0 members and 1 guests)