Using the Loop example from Excel Help -
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
I've tried to use it to extract data FROM Excel by assigning a variable to:
Variable = ActiveCell.Value.

This doesn't work. Also, the ActiveCell not appear to interate through the
For Each Loop.

How do I extract a range of data into a VBA array?

You're not changing the activecell.

But you could use:
Variable = C.Value.



Bob Allen wrote:

Using the Loop example from Excel Help -
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
I've tried to use it to extract data FROM Excel by assigning a variable to:
Variable = ActiveCell.Value.

This doesn't work. Also, the ActiveCell not appear to interate through the
For Each Loop.

How do I extract a range of data into a VBA array?

--

Dave Peterson

Try this:

Dim arrRange As Variant
arrRange = ActiveSheet.Range("A1:D10").Value

--JP

On Jul 31, 4:20*pm, Bob Allen
wrote:

How do I extract a range of data into a VBA array?

Sub ifvaluelessthan()
mv = 0.01
On Error Resume Next
For Each c In Worksheets("Sheet5").Range("A1:D10")
If Not IsEmpty(c) And IsNumeric(c) And Abs(c) < mv Then c.Value = 0
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Allen" wrote in message
...
Using the Loop example from Excel Help -
For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
If Abs(c.Value) < 0.01 Then c.Value = 0
Next
I've tried to use it to extract data FROM Excel by assigning a variable
to:
Variable = ActiveCell.Value.

This doesn't work. Also, the ActiveCell not appear to interate through
the
For Each Loop.

How do I extract a range of data into a VBA array?

On Fri, 31 Jul 2009 13:20:01 -0700, Bob Allen
wrote:

How do I extract a range of data into a VBA array?


Here's a simple method:

Option Explicit
Sub GetArray()
Dim rg As Range
Dim a As Variant
Set rg = Range("A1:D10")
a = rg.Value
End Sub

a will turn into a 2D array, with each element containing the value in one of
the cells in rg.

a : Variant/Variant(1 to 10, 1 to 4)
--ron