ExcelBanter - String Array

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - String Array (https://www.excelbanter.com/excel-programming/408389-string-array.html)

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

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

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 -

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 -