VBA: XLSpecialCells

Rennie · Oct 23rd 2003

Hi everyone,

I am trying to test a list of named cells to determine if they contain formulas or text values.

Can anyone help me write a macro to do this.

My attempt at this returned a type mismatch error.

Sub TestCell()
If ActiveCell.Type.xlCellTypeFormulas Then
ActiveCell.FormulaR1C1 = "Formula"
Else
ActiveCell.FormulaR1C1 = "Not a Formula"
End If
End Sub

Thanks,
Rennie

Pesky Weasel · Oct 23rd 2003

Rennie,

From your code you seem to be trying to place the word Formula/Not a Formula in the activecell that it is checking - which would then destroy the formula.

This will check every named range in the workbook and then place the text 'Its a Formula' in the cell beside the cell in the named range that contains a formula.

Use a copy of your work to test this.

Regards
Weasel

Code

Sub IsItFormula()


Dim wbBook As Workbook
Dim nName As Name


Set wbBook = ActiveWorkbook


For Each nName In wbBook.Names
If Range(nName).HasFormula Then
Range(nName).Offset(0, 1) = "Its a formula"
End If


Next nName


End Sub

Display More

Rennie · Oct 23rd 2003

Thank you very much Weasel.

I have pated a list of all the names (about 200) in a new sheet. What I want to do is to run a macro that will state if the name listed has a formula.

Name Range Formula?
1 It's a formula
2 Not a formula

Etc.

I have attempted to modify your code to do this without any luck. Can you please help.

Your kindness is much appreciated.

Thanks,
Rennie

Pesky Weasel · Oct 23rd 2003

Sure, no problem.

How did you wan the macro to state this to you? One at a time, all together, in a message box, in cells on a worksheet?

Regards
Weasel

Edit: Off to see a show now, i will reply later if sober enough :cheers: :beergrin: :drink: :dance3: :drunk: ................ :barf: :2puke: :puke3: ........... :sleep:

Rennie · Oct 23rd 2003

Thank you again Weasel.

Can I have this listed in a column alongside my pasted list?

Eg.

Name Range Formula?
ACL ='Call Statistics'!$B$10
ACW ='Call Statistics'!$C$10

Thanks,
Rennie

Pesky Weasel · Oct 23rd 2003

Rennie,

I must admit, Im getting little confused, are these names in your list just a text listing of names that are contained in the workbook?

Regards
Weasel

Pesky Weasel · Oct 23rd 2003

Ok, try this:

You will need to select (highlight) the cells that contain your range names.

Code

Sub nameFormulas()
Dim cell As Range
Dim x As String
For Each cell In Selection
x = Range(cell).Formula
cell.Offset(0, 1) = "'" & x
Next cell
End Sub

Regards
Weasel

Rennie · Oct 23rd 2003

Sorry Weasel,

Let me explain. I inserted a new sheet in the work book and did insesrt>name>paste>paste list. This is how I got the list of 200 names.

Thank you for the code I'll test it out.

Rennie

Rennie · Oct 24th 2003

Hi Weasel,

I have worked it out (After 2 Hours!). Below is the code I came up with. Thank you verymuch for your help.

Best regards,
Rennie

'
Sub FormulaCells()
'
' FormulaCells Macro
' Macro recorded 24/10/2003 by rthangiah
'

'
Do Until ActiveCell.Text = "X"
If Range(ActiveCell.Value).HasFormula = True Then
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "Yes"
Else
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "No"
End If
ActiveCell.Offset(1, -2).Range("A1").Select
Loop
End Sub

VBA: XLSpecialCells

Participate now!

Share