String Array

[Karen53]

Hi,

I'm going around with this. What am I doing wrong?

Option Explicit

Private ProRataShareCell As String


Private Sub Worksheet_Activate()

Dim N As Long
Dim ProRataArray As String

ProRataArray = Array _("F30", "G30", "H30", "I30", "J30", "K30", "L30",
"M30", "N30", "F36", "G36", "H36", "I36", "J36", "K36", "L36", "M36", "N36")

For N = 1 To 18
If Application.Match(Me.Range("K43").Value, ProRataArray(N), 0) Then

ProRataShareCell = ProRataArray(N)
Debug.Print ProRataShareCell
End If
Next N

End Sub

 

[Gary Keramidas]

this works for me, but i don't know if it's what you want.

Private Sub Worksheet_Activate()

Dim N As Long
Dim ProRataArray As Variant

ProRataArray = Array("F30", "G30", "H30", "I30", "J30", "K30", "L30", "M30", _
"N30", "F36", "G36", "H36", "I36", "J36", "K36", "L36", "M36", "N36")

For N = 0 To 17
On Error Resume Next
If Application.Match(Me.Range("K43").Value, ProRataArray(N), 0) _
Then
ProRataShareCell = ProRataArray(N)
Debug.Print ProRataShareCell
On Error GoTo 0
End If
Next N

End Sub

 

[Ivyleaf]

Hi Karen,

I am assuming you are trying to check if the value in K43 exists in
the array of cells you have specified, i which case you would need
something like this:

Option Explicit
Private ProRataShareCell As String

Private Sub Worksheet_Activate()

Dim N As Long
Dim ProRataArray()

ProRataArray = Array("F30", "G30", "H30", _
"I30", "J30", "K30", "L30", "M30", "N30", _
"F36", "G36", "H36", "I36", "J36", "K36", _
"L36", "M36", "N36")


For N = LBound(ProRataArray) To UBound(ProRataArray)
If Me.Range("K43") = Range(ProRataArray(N)).Value Then


ProRataShareCell = ProRataArray(N)
Debug.Print ProRataShareCell
End If
Next N


End Sub

Or, use the Find method... something like:

Option Explicit
Private ProRataShareCell As Range

Private Sub Worksheet_Activate()

Dim ProRataRng As Range

Set ProRataRng = Union(Range("F30:N30"), Range("F36:N36"))

With ProRataRng
Set ProRataShareCell = .Find(What:=Me.Range("K43"), After:=ProRataRng
_
.Areas(ProRataRng.Areas.Count).Cells(ProRataRng _
.Areas(ProRataRng.Areas.Count).Cells.Count))
End With

End Sub

Which will avoid a loop and just find the first cell that matches.

Cheers,
Ivan.

 

[Karen53]

Hi all,

Yay!! It's working. Thank you all for your help on this.