ISNumber VBA

T

trickdos

Hey Guys,

I am interesting in writing a macro to cycle through all the sheets o
a workbook and see in each cell if it is a formula or if it is
hardcode. If it is a hardcode, can it create a list on a seperat
sheet of the cell address.

I appreciate any help you can give me.

Thanks,
Bret
 
F

Frank Kabel

Hi
for checking if a cell contains a formula you can use
msgbox activecell.hasformula
 
J

JE McGimpsey

one way:

Public Sub ListConstants()
Dim wsSheet As Worksheet
Dim rCell As Range
Dim rDest As Range
Dim rConstants As Range
Dim nNumSheets As Long
Dim i As Long

nNumSheets = Sheets.Count
With Worksheets.Add(After:=Sheets(nNumSheets))
.Name = "Constants"
With .Range("A1:C1")
.Value = Array("Sheet", "Cell", "Value")
.Font.Bold = True
End With
Set rDest = .Range("A2")
End With
For i = 1 To nNumSheets
On Error Resume Next
Set rConstants = Worksheets(i).Cells.SpecialCells( _
xlCellTypeConstants)
On Error GoTo 0
If Not rConstants Is Nothing Then
For Each rCell In rConstants
With rCell
rDest.Value = .Parent.Name
rDest(1, 2).Value = .Address(False, False)
rDest(1, 3).Value = .Value
End With
Set rDest = rDest(2, 1)
Next rCell
End If
Next i
End Sub
 
J

JE McGimpsey

If you only want numbers (not text), replace

Set rConstants = Worksheets(i).Cells.SpecialCells( _
xlCellTypeConstants)

with

Set rConstants = Worksheets(i).Cells.SpecialCells( _
xlCellTypeConstants, xlNumbers)
 
T

trickdos

I appreciate your help. It listed the constants, which is very helpful.
Now I have to go through them and make sure they all make sense. ie
not take a date converted to a number.

If possible, i also need hardcodes within formulas, but I assume thi
is much harder.

I did my best at writing my macro, and I came close, but it was muc
longer and much more difficult. I appreciate your prompt response
 
A

Adria Draughn

I posted this a while back. You may be able to use it. It identifies
different cell types including hard codes inside formulas.
 
F

Frank Kabel

Dana said:
A little of topic. Here is an interesting article on Excel
converting Dates to Numbers. I can't find who posted this link
recently to give credit. It's an interesting article as it serves as
a good reminder:
Click to expand...

Hi Dana
it was Harlan Grove who posted this some days ago :)

and I agree: Very interesting article!

Frank
 
D

Dana DeLouis

Thanks Frank. I was almost positive it was Harlan, and that's what I used
in most of my searches. However, for some reason, I still can not find that
article anywhere!! Hmm. I don't know.

For the op, if you run into problems trying to figure out if a number is a
date, sometimes using .Value2 can help. It would depend on what you are
using of course.

Dana
 
Top