Operating on a Selected Range

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

We have a macro that indents and inserts a hyphen onto
text, i.e., "Johnson" becomes " - Johnson". We would
like the macro rather than operate on a single cell to
cycle through each cell in the selected range.

I am also curious to know what Visual Basic method moves
the active cursor down one cell.

Thank you for the help.

Kevin Sprinkel
Becker & Frondorf
 
ActiveCell.Offset(1,0).Select

but you don't need to do that to work with a cell

Sub AddHyphen()
Dim cell as Range
for each cell in Selection
if not cell.hasFormula then
if instr(cell.value,"-") = 0 then
cell.Value = "- " & cell.Value
End if
End if
Next
End Sub
 
To cycle through cells, you can try the For Next
convention. For example,

For Each c In Range(RefEdit1.Value).Cells
c.Value = "'" & c.Value
Next

c is a variable in the above code.

To move the cursor down one, you can try

SendKeys "{Down}", True 'Moves the cursor down to the
next cell

Hope this helps.

Keith Lorenzen
 
Back
Top