Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Array
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a string as a formula (for an array) | Excel Programming | |||
String Array initialisation | Excel Programming | |||
String array issues. Need help... | Excel Programming | |||
Passing a String in Array to Range as String | Excel Programming | |||
Convert a string to an array | Excel Programming |