Option Compare Text
" [: G# Y8 K/ T" I5 D# w( h9 E4 t, X
Public Function GSXS(Ref), Y5 k; W1 b+ O4 t$ x5 }: B
6 U$ ~9 {1 Z( L5 Z3 N GSXS = Ref.Formula% g+ q3 |7 ?. `7 b
. j, r6 i6 n& r, c3 BEnd Function
& @6 j1 ~1 I7 O$ _5 k1 {' j, t A7 ^ ?1 E, h1 K) g
Public Function ZZL(RowHead, ColHead, Dummy)% ?$ _# i4 r# X) E
4 A/ u" k8 a4 b7 E7 UDim Values(20) As Variant( \& O: e, u* p* @! T3 f% Y$ Y; ~
Dim PrevData(20) As Variant Z4 D5 H* b$ S' E5 z# r
Dim LE(20) As Integer+ j6 B+ G, b) \
" G" S6 X; N5 v! R* x2 W4 kOn Error GoTo err_handler1
) J/ h ?& U# N4 d# A, v2 E. s' Do the vertical selection from rows! h- c' r5 R! a m
If RowHead.Rows.Count = 1 Then
* L: |0 b' B% d1 y0 V& \ L; O rindex = RowHead.Row ' first argument is any cell on the row of possible values- X# U# e; c& P$ l' M
Else# y% z! `" B- @
' Store the values to be compared with each column
4 K3 }: G& F) I' {: e- r' { For ii = 1 To RowHead.Columns.Count% Z. J _& a( t# b
rngname = RowHead.Cells(1, ii)+ F( K4 G+ L. l; m5 r2 }! V
LE(ii) = InStr(rngname, "<=")
9 [# o+ r+ ?0 A# G- C If LE(ii) > 0 Then
. F1 Y" U. D# w+ y rngname = Mid(rngname, 1, LE(ii) - 1)
6 Q) ^- X, H- a; w: Y6 L- u6 c End If
3 b# h. E* U/ T0 W0 l( C9 d Values(ii) = Range(rngname)' N( J7 Y: c5 ?/ {5 X7 c' i. n5 X
'debug.Print "Variable:" & rngname & " is:" & Values(ii) a, x/ s' Z3 Z8 x) X; o
PrevData(ii) = "" ' initialise
3 p% y( @. z |, ]# G Next ii
7 g! @8 [# Z" y) t0 [- V
2 n+ a0 X- Y8 k1 l2 T/ { T rindex = 2- a- D) r7 e6 E7 i$ C& S& X
'debug.Print RowHead.Columns.Count; m" X! ~0 x8 Q/ K! @% U3 G" L
Match = False
4 |) M D" F# F9 l For r = rindex To RowHead.Rows.Count2 a+ m8 x( G9 Q- M" @& K: L
For c = 1 To RowHead.Columns.Count ' for each dimension( r# ]- g7 Y! y- X% T( A
data = RowHead.Cells(r, c)
; ^6 l# p) ~. O1 w If data = "" Then
7 d# S/ t, p6 n! l: e& ~/ |6 J 'debug.Print "Empty cell found: using " & PrevData(c)6 M5 w5 N+ i: z- ^+ L9 y
' use the last valid cell in this column/ y6 z5 ^$ j' D4 W
' (this is to handle merged cells)7 t+ w* B+ ?; n& y
data = PrevData(c)
4 }3 ?/ M6 }' ] End If5 }9 [: c* o9 z9 |1 |6 y9 D4 Z
'debug.Print "data:" & data
5 L7 Q2 W4 x" n: W2 t& H4 D! P PrevData(c) = data ' save for use by empty cells. a! t/ }) E' J! o* e* C( x
If data = Values(c) Or (data > Values(c) And LE(c) > 0) Or data = "*" Then
- Q, ?( P. i% X5 T0 n9 ` If c = RowHead.Columns.Count Then ' All columns match - It's a go7 s) k+ t* x' Q8 {9 o9 d
Match = True
; L/ r6 F& u% e' T End If4 W8 _* W1 E& E1 m" T& l' ]
Else ' This column doesn't match - go to the next row
0 J( a( x6 X, w5 N( U Match = False0 _! P1 [! q3 s8 S4 ?, {+ @" `+ J# Q3 ^
Exit For
Y& U [, {9 |0 c End If
1 {! S( `6 n8 G Next c
& x/ A. `7 d5 s' f. f If Match = True Then ' Don't search any more rows! W# r/ u( _( I$ W# R0 Q
rindex = r
% s4 ^4 p5 g3 B u( `' O: }+ t5 a Exit For u& y! P6 L6 `7 z/ C9 w7 P5 [2 h# T+ G7 i
End If! v: B; R4 p4 r+ ^9 i7 ]
Next r- J# w3 ^5 c0 a) ?4 v+ G; Y X
8 O: r5 }/ S* c. g% b- |6 m3 f& L' R If Match = False Then ' Didn't find a matching set of values
8 t) r8 q! s e9 ?0 v' \* Q ZZL = "No match for rows"
& \( Q2 f A0 q5 Q Exit Function
; C! o& R% J3 ^+ Z: ?5 O End If
$ t7 z* p5 b$ i7 D- [8 G4 ? z+ r$ q a1 [
rindex = rindex + RowHead.Row - 1 ' make absolute index4 @) l) H! k. ` m. a5 V
End If! E, { J2 h5 v4 c' {# r% f
- v$ y% q6 G% x/ g: [# V( j! e' Do the horizontal selection from columns
; o6 T" Z' r5 v8 R( ~If ColHead.Columns.Count = 1 Then
! E! |7 ]$ a& s- ?1 l cindex = ColHead.Column( u4 h$ g3 M2 L
Else
* g& ~! @3 s( R' u/ B( O ' Store the values to be compared with each row of the header% k0 d U. Y2 L- o h$ E- T% s
For ii = 1 To ColHead.Rows.Count. ]/ t7 R5 N5 _% H4 T5 a+ e3 n2 C
rngname = ColHead.Cells(ii, 1)* S* d6 _0 ~% F* }6 ?% ^" T/ A3 b
LE(ii) = InStr(rngname, "<=")
5 a ]# V+ C1 r! Q" \ If LE(ii) > 0 Then
) Z+ W0 d- T* o rngname = Mid(rngname, 1, LE(ii) - 1)
' a1 s8 ^3 ]' u% D3 e End If
# E C! O! I8 V2 o Values(ii) = Range(rngname)
; |& J& A7 Q8 r; A7 ] 'debug.Print "Variable:" & rngname & " is:" & Values(ii)
1 L( o9 @, o# Z+ f; ^- Y& }% n PrevData(ii) = "" ' initialise
; Z) v5 {' x5 ?3 {3 o$ c Next ii4 g3 E6 X+ w3 Q* `9 t m- Q& M
" K4 t+ R$ b; l( E0 P, R; x* h cindex = 2, H7 @2 ]; V6 E
'debug.Print ColHead.Columns.Count
9 N7 F8 _" Q' Q Match = False% t6 H1 {$ b, [: J* Q
For c = cindex To ColHead.Columns.Count$ \5 K" n$ @. l: Y; B$ \
For r = 1 To ColHead.Rows.Count ' for each dimension
& Z2 I/ O$ @, \3 r' o( _ data = ColHead.Cells(r, c)9 W2 g! N. Q( q. L
If data = "" Then; b0 N4 O; H0 S6 F7 a0 D
'debug.Print "Empty cell found: using " & PrevData(r)- v; C7 U# k$ O
' use the last valid cell on this row
8 J- ]3 p, X/ a$ @; X N* w ' (this is to handle merged cells)
/ L+ k- e4 u7 B% @% l data = PrevData(r)
2 u" b4 {5 {$ ~! B& }( M$ B7 B* A- z End If
' T; e- N* Q' b, i 'debug.Print "data:" & data( l6 j' l, L; v3 _
PrevData(r) = data ' save for use by empty cells
' G* F" C: A1 F" t If data = Values(r) Or (data > Values(r) And LE(r) > 0) Or data = "*" Then6 N4 U' Y5 G' u
If r = ColHead.Rows.Count Then ' All rows match - It's a go
; ]7 U( c$ S! P Match = True
0 C1 K G" A N& Y End If) A2 ^/ e# c& ]9 l9 [ o$ V; w" w
Else ' This row doesn't match - go to the next column
" t$ ]0 z, Z" x8 S- w Match = False3 u) @- B( w9 \! Z- t) q
Exit For9 Z- T$ [3 x' @$ ~4 d# {# h
End If# C; E$ w! v% L* p
Next r
0 A% m& {3 C$ t5 E O1 X If Match = True Then ' Don't search any more columns
7 }& F" {0 ^! \$ \9 u; i5 D' p cindex = c0 ^3 L) X0 U: ^' j) T9 h
Exit For- G, O" E) r% U# ]4 Z8 l- g
End If( R$ k8 \6 J) d* e' B- x" G8 {
Next c& \ b1 e; V" @2 v
/ K9 f+ x2 R' D' ] If Match = False Then ' Didn't find a matching set of values
. j, C+ q9 M, R( t) P7 ] ZZL = "No match for columns"
. R G" h5 S# H, z+ v& o Exit Function
. S& e' l# {% L' u- e' E' M End If
, A9 ?1 w8 _+ ?. o/ C: ]' a
" ?; e C* Q% O2 ]. @8 m cindex = cindex + ColHead.Column - 1
5 _1 B* j/ A! o; HEnd If
' n! W( Z7 Q! W5 |* P& k; Q/ ^' I/ M; ~# t' t# n
' Return the cell value from Table' D& O, w, w0 I1 D$ Y
'debug.Print "Answer is in (R,C): " & rindex, cindex
3 o2 x% _* g0 ~. p' aZZL = ActiveSheet.Cells(rindex, cindex)
0 S3 Y) g( _0 F$ ] f'debug.Print "Answer is : " & ZZL( w3 n. v# X7 m) p9 u0 e7 i- S
Exit Function
5 G' r$ q" D. N, Q9 g/ \0 N
8 a/ k* s/ m& B3 ]err_handler1:' m: P0 x% ~. G, e" Z; M
ZZL = "Error on range '" & rngname & "'"
% T9 i6 W- ]1 ?" ]0 E0 v8 K% W+ y2 J* k; p7 d
End Function" E2 \0 A! D3 ?' w
7 K( O# p0 i y" @6 H |