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

--
Message posted from http://www.ExcelForum.com

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

--
Regards
Frank Kabel
Frankfurt, Germany

"trickdos " schrieb im
Newsbeitrag ...
Hey Guys,

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

I appreciate any help you can give me.

Thanks,
Brett


---
Message posted from http://www.ExcelForum.com/

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



In article ,
trickdos wrote:

Hey Guys,

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

I appreciate any help you can give me.

If you only want numbers (not text), replace

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

with

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


In article ,
JE McGimpsey wrote:

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

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

--
Message posted from http://www.ExcelForum.com

Now I have to go through them and make sure they all make sense. ie.
not take a date converted to a number.

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:

Excel ate my DNA
http://www.theregister.co.uk/2004/07..._vanishing_dna

Dana DeLouis


"trickdos " wrote in message
...
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 this
is much harder.

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

Dana DeLouis wrote:
Now I have to go through them and make sure they all make sense. ie.
not take a date converted to a number.

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:

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

and I agree: Very interesting article!

Frank

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

"Frank Kabel" wrote in message
...
Dana DeLouis wrote:
Now I have to go through them and make sure they all make sense. ie.
not take a date converted to a number.

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:

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

and I agree: Very interesting article!

Frank