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.

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.

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.

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.