String Array
Hi all,
Yay!! It's working. Thank you all for your help on this.
--
Thanks for your help.
Karen53
"Ivyleaf" wrote:
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.
On Mar 27, 3:04 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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- Hide quoted text -
- Show quoted text -
|