How do you create and pass values to an array? 1

[qjd2004]

Hiya,

can some explain how to do arrays in VBA to me? In 1 of my modules I'm using a simple procedure to check for any values that are different from the first record in that range. If they are, then it adds them to the another cell.

There's never more than 2 different values so I didn't need to go any further with it.

Now, I want to pass 2 ranges into 2 arrays:

range1 = "I1:I10"
range2 = "J1:J10"

I want to check each cell in range1, if there's a value > 0.0000 then the corresponding cell in range2 should be = range("H5").Value, otherwise it should be = 0.00

I cannot do this by putting in a formula like Range.Value = "=IF(I1 > 0.0000, "=$H$5", 0.00)"
because all the cells in the worksheets do not allow formulas in (due to a control from another application) so I must put the value in directly.

Can someone assist me please? Sorry if this explanation is not very clear.

Here is my starting point for looking at 1 range (not yet using an array becauase I don't now how to).

Public Sub GetSomeValues()

Dim LastRow As Long
Dim LastCell As Range
Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
Dim MyRange As String
Dim r As Variant
Dim cl As Object
Dim y As String

LastRow = LastCell.Row
y = Range("F5").Value

MyRange = "C14:C" & LastRow
Set r = Range(MyRange)

    For Each cl In r
    
        If cl.Value <> y Then
            Range("F6").Value = cl.Value
            Exit For
        End If
    
    Next cl

Anyone out there help me please?

 

[xlbo]

You only need 1 array

Option Base 1
Sub ArrayExplanation()
Dim Arr() As Variant
Arr() = Range("I1:I10")

For i = LBound(Arr()) To UBound(Arr())
 If Arr(i, 1) > 0# Then
   Range("J" & i).Value = "Whatever Value to apply"
 Else
   Range("J" & i).Value = 0#
 End If
Next i
End Sub

Rgds, Geoff

 

[qjd2004]

Hi Geoff,

thanks very much. That works a treat and has got me started learning arrays too.

I'm much obliged!

Timothy.

 

[xlbo]

No probs.
Have a look in the help file for
Option Base
Ubound
Lbound
Redim
Preserve

These are all common functions when using arrays

Rgds, Geoff