PDA

View Full Version : Solved: Using is empty on a range



grichey
05-26-2009, 05:37 AM
The goal is to highlight a range, run the macro, have all cells that are just #'s (not formulas) AND are not presently blank/empty multiplied by 1000.

I'm getting an error on the If and I know it has to do with my use of IsEmpty but I'm not sure how to rectify.

Thanks!

Option Explicit
Sub puttothousands()
Dim c As Range

For Each c In Selection
If c.HasFormula = False And c.IsEmpty = False Then
c = c * 1000
End If
Next c
End Sub

xld
05-26-2009, 05:40 AM
Sub puttothousands()
Dim c As Range

For Each c In Selection
If Not c.HasFormula And Not IsEmpty(c.Value) And IsNumeric(c.Value) Then
c = c * 1000
End If
Next c
End Sub

grichey
05-26-2009, 05:58 AM
Thanks for helping shake the rust off. First use of VBA in months...

Krishna Kumar
05-26-2009, 06:03 AM
Hi,

A non-loop approach..

Dim r As Range, tmp As Range
Set r = Selection
Set tmp = Range("iv1") ' a temporary cell
tmp = 1000
tmp.Copy
On Error Resume Next
r.SpecialCells(xlCellTypeConstants, 1).PasteSpecial , Operation:=xlMultiply
On Error GoTo 0
tmp.Clear
Application.CutCopyMode = 0

HTH

grichey
05-26-2009, 06:20 AM
Ya I realized I could've done the paste special. The loop method is nice and more easily modified for adding additional operations in the future.

Thanks!