I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

If it is the same formula in each cell, can't you just ensure that is
has been entered correctly in the first cell, and then copy down to
the other cells?

Hope this helps.

Pete

On Aug 19, 3:49*pm, Jennifer
wrote:
I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

Select the range of cells and run this macro:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
MsgBox (r.Address)
End If
Next
End Sub

It will tell you the address of any cell in your range that does not have an
array formula.
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

Thank you that was exactly what I was looking for. Now that I see this can be
done. Is it possible to just highlight the cells in , lets say yellow instead
of the message box ?

"Gary''s Student" wrote:

Select the range of cells and run this macro:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
MsgBox (r.Address)
End If
Next
End Sub

It will tell you the address of any cell in your range that does not have an
array formula.
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

Yes. Erase the old version first and then:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
r.Interior.ColorIndex = 6
End If
Next
End Sub
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

Thank you that was exactly what I was looking for. Now that I see this can be
done. Is it possible to just highlight the cells in , lets say yellow instead
of the message box ?

"Gary''s Student" wrote:

Select the range of cells and run this macro:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
MsgBox (r.Address)
End If
Next
End Sub

It will tell you the address of any cell in your range that does not have an
array formula.
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

What Gary''s Student provided works great, what I am thinking would be great
instead of a message box, for each cell tha tis not an array have the macro
highlight the cell a color. Once the code is done running user can go back
and update those cells,instead of wrting each down, than going back.
What would I add or remove in the code to do this ?


"Dana DeLouis" wrote:

check without error if all the formulas were entered as an
array

Don't know if you could use something like this with your layout.
A1 would have to be part of an Array formula for this idea to work.
It idea comes from F5 (Goto) : Special ... | Current Array

Sub Demo()
With [A1]
MsgBox .CurrentRegion.Address = .CurrentArray.Address
End With
End Sub

--
Dana DeLouis


"Jennifer" wrote in message
...
I have a range of cells, over 500, that all have array formula. Is there
an
easy and fast way to check without error ifall the formulas were enterd
as an
array ? Using ctrl ` is to time consuming and room for error when
checking
the range.

I did have one additional inquiry. What does the "r" stand for? I assuming
array, but how was this assigned in the macro?

"Gary''s Student" wrote:

Yes. Erase the old version first and then:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
r.Interior.ColorIndex = 6
End If
Next
End Sub
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

Thank you that was exactly what I was looking for. Now that I see this can be
done. Is it possible to just highlight the cells in , lets say yellow instead
of the message box ?

"Gary''s Student" wrote:

Select the range of cells and run this macro:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
MsgBox (r.Address)
End If
Next
End Sub

It will tell you the address of any cell in your range that does not have an
array formula.
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

Perfect thank you so much for your help and so quickly !

"Gary''s Student" wrote:

Yes. Erase the old version first and then:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
r.Interior.ColorIndex = 6
End If
Next
End Sub
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

Thank you that was exactly what I was looking for. Now that I see this can be
done. Is it possible to just highlight the cells in , lets say yellow instead
of the message box ?

"Gary''s Student" wrote:

Select the range of cells and run this macro:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
MsgBox (r.Address)
End If
Next
End Sub

It will tell you the address of any cell in your range that does not have an
array formula.
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

The r is just a range. Selection can represent a pile o' cells the user has
selected. The For loop with r allows us to individually examine each cell
(?individually?)
--
Gary''s Student - gsnu200801


"Jennifer" wrote:

I did have one additional inquiry. What does the "r" stand for? I assuming
array, but how was this assigned in the macro?

"Gary''s Student" wrote:

Yes. Erase the old version first and then:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
r.Interior.ColorIndex = 6
End If
Next
End Sub
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

Thank you that was exactly what I was looking for. Now that I see this can be
done. Is it possible to just highlight the cells in , lets say yellow instead
of the message box ?

"Gary''s Student" wrote:

Select the range of cells and run this macro:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
MsgBox (r.Address)
End If
Next
End Sub

It will tell you the address of any cell in your range that does not have an
array formula.
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.

Thank you for all your help

"Gary''s Student" wrote:

The r is just a range. Selection can represent a pile o' cells the user has
selected. The For loop with r allows us to individually examine each cell
(?individually?)
--
Gary''s Student - gsnu200801


"Jennifer" wrote:

I did have one additional inquiry. What does the "r" stand for? I assuming
array, but how was this assigned in the macro?

"Gary''s Student" wrote:

Yes. Erase the old version first and then:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
r.Interior.ColorIndex = 6
End If
Next
End Sub
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

Thank you that was exactly what I was looking for. Now that I see this can be
done. Is it possible to just highlight the cells in , lets say yellow instead
of the message box ?

"Gary''s Student" wrote:

Select the range of cells and run this macro:

Sub findarray()
For Each r In Selection
If r.HasArray = False Then
MsgBox (r.Address)
End If
Next
End Sub

It will tell you the address of any cell in your range that does not have an
array formula.
--
Gary''s Student - gsnu200800


"Jennifer" wrote:

I have a range of cells, over 500, that all have array formula. Is there an
easy and fast way to check without error ifall the formulas were enterd as an
array ? Using ctrl ` is to time consuming and room for error when checking
the range.