String Array
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
--
Gary
"Karen53" wrote in message
...
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
--
Thanks for your help.
Karen53
|