Option Compare Text
# P+ @/ {, U2 Q7 p8 @
$ w- O# s h) F0 k& TPublic Function GSXS(Ref)
5 d! G. ^8 r7 E
5 V$ q$ C7 s6 _ GSXS = Ref.Formula! b1 @6 h% w$ l* c; E p5 L: H
9 G& ?/ x: M1 _9 m' j7 k
End Function) X' y$ ?2 |5 c. f, N8 s
& s+ M3 d) W& G- U% z* kPublic Function ZZL(RowHead, ColHead, Dummy)0 V: p) K2 W! a5 K& N+ J
% R X! n8 b% \9 ~+ l% T7 l
Dim Values(20) As Variant
, s% P) \4 n2 J7 W& b$ u9 uDim PrevData(20) As Variant
1 E8 y' p: G# d8 ?; h L& u2 W/ YDim LE(20) As Integer
" e( R- J9 ~ W! |4 c$ E" M. F+ W4 F* ^: A) i6 W
On Error GoTo err_handler1# U+ X! q# h. @- }; h
' Do the vertical selection from rows
+ i# a' u1 U: { k# D. u2 b! K, gIf RowHead.Rows.Count = 1 Then; t: K! J( c. }2 l9 [" K4 b; g
rindex = RowHead.Row ' first argument is any cell on the row of possible values
, E2 u( n* \3 ^) _# dElse
. R+ f# G7 @/ Y- ] ' Store the values to be compared with each column
1 [* `: h, j, M0 x) U For ii = 1 To RowHead.Columns.Count
. x9 E4 r& } [2 {; H1 Y rngname = RowHead.Cells(1, ii), b& j0 k8 k% e; G
LE(ii) = InStr(rngname, "<=")) h7 P' x* k$ _# n1 d1 h
If LE(ii) > 0 Then$ S; @6 ~( l2 }2 b% c
rngname = Mid(rngname, 1, LE(ii) - 1)
: I# }/ h" E5 [ _5 `) z$ ? End If# p& b! n$ t# ?' Q U4 i
Values(ii) = Range(rngname)
( b: G3 o$ a5 [5 c 'debug.Print "Variable:" & rngname & " is:" & Values(ii)
n) |& y p+ v6 q4 P/ n) Q PrevData(ii) = "" ' initialise4 Y) L' P" f0 ~: k5 R8 m. f: o
Next ii
. F" r+ [( p" K7 p4 F+ b! E+ ?1 L! k' y) ]$ t; F4 N
rindex = 2
0 z" P+ y* p$ n& ^& b. o 'debug.Print RowHead.Columns.Count
$ E+ G, q/ ^; v3 l: E Match = False
2 |9 V M8 D# D9 Z, y For r = rindex To RowHead.Rows.Count# k* _% S0 d# p& E: p; e" `' ~
For c = 1 To RowHead.Columns.Count ' for each dimension
/ z- {/ W2 S0 B data = RowHead.Cells(r, c)
* c, R" o j, n) v8 k; E3 w! d If data = "" Then
1 F$ f( Q, Y8 _. x! c7 H 'debug.Print "Empty cell found: using " & PrevData(c); s! O0 ^, s4 T$ u9 i6 T1 m
' use the last valid cell in this column) g6 J. O, G: q4 V+ `6 M
' (this is to handle merged cells)
! o; q" ^0 V0 P b" k data = PrevData(c)
$ U) V+ X/ H5 O4 `$ X End If
! b2 Z B) j1 q: h 'debug.Print "data:" & data
8 r0 O$ V; D9 w1 Q( e1 \8 y" g# F PrevData(c) = data ' save for use by empty cells8 G2 H- U- H" G7 z" i: t( U
If data = Values(c) Or (data > Values(c) And LE(c) > 0) Or data = "*" Then
- W. L4 m: L2 Y+ q$ k1 _1 [4 L If c = RowHead.Columns.Count Then ' All columns match - It's a go" c8 }/ q) A& ?5 K
Match = True' ]6 t; o; d* u6 j$ _' Y
End If
# c0 i, F' n; Y) p% S7 m Else ' This column doesn't match - go to the next row. K1 _# L7 o3 L: V; b6 l* [3 b+ _6 Y
Match = False
1 y2 Q* @) t$ B0 X. a! _ Exit For
3 @& ~( `9 L) v+ Q End If
6 ?6 U, ]/ Z6 X2 r: G; a Next c, p" Z8 B9 f9 R" p/ ]! ~! Y
If Match = True Then ' Don't search any more rows. d# C( x. }4 [+ q0 V" {3 ^. h
rindex = r7 a N- ?( c! i6 w6 Q- X' V1 @7 r% n
Exit For
$ R* }8 F& U# P- S" y; [ End If. g, u, r: \, b4 M8 O: y
Next r6 }) I$ |# y" x- {" l! v' ]
& f3 R) A2 t0 |: [+ I8 C3 q$ R
If Match = False Then ' Didn't find a matching set of values
6 X% a: x( N. m( X, n! Y ZZL = "No match for rows"
' w3 e$ B5 o2 ^( S- w Exit Function- a" r8 G5 `3 I2 }
End If" C; l, W# O5 E/ K- e2 n
9 d8 h% ` s' e: Z" a
rindex = rindex + RowHead.Row - 1 ' make absolute index
7 ^; N0 t+ o8 C' g& I# SEnd If
- Q4 {3 D, m, b4 R: d
' G4 b( b( |$ y5 V* N$ M( q/ M* ~' Do the horizontal selection from columns! e( l k# p9 U" h3 r
If ColHead.Columns.Count = 1 Then1 c5 a' d$ l; P
cindex = ColHead.Column
: i9 x' x4 t7 M+ K" v1 wElse
0 B N+ P5 v: j2 Q2 z ' Store the values to be compared with each row of the header$ T% M/ T& O, O( V* O
For ii = 1 To ColHead.Rows.Count
/ i3 P$ E: Q! Z5 \1 m0 B rngname = ColHead.Cells(ii, 1)
% `$ T/ I$ s3 P; m LE(ii) = InStr(rngname, "<="), t, f; L$ A, `' b: f
If LE(ii) > 0 Then
3 j# B3 X0 m7 B1 k, Z- Z# W rngname = Mid(rngname, 1, LE(ii) - 1)( {! ^& `) X+ v$ S
End If
^" c4 {. ?" Y$ a% m Values(ii) = Range(rngname)- h* t+ K+ D7 O! I. s0 f+ i
'debug.Print "Variable:" & rngname & " is:" & Values(ii)
- @% m6 B8 w# L/ Z, \; T V PrevData(ii) = "" ' initialise
! K2 k# C7 L. J Next ii
, s% s) [1 X" g) p# f0 @. q% e$ u' Z& p$ t1 k9 u
cindex = 2
% B$ \6 j/ }8 \! \8 L9 H 'debug.Print ColHead.Columns.Count
. h, Y+ v" D/ ^" e$ {. W Match = False9 @/ a, q- L/ m4 e |
For c = cindex To ColHead.Columns.Count
# G0 r" q2 o" M z; T( n% ^: L For r = 1 To ColHead.Rows.Count ' for each dimension
. P5 _! K+ U1 r8 M8 O; W- R& V/ t7 ^9 Q: ~ data = ColHead.Cells(r, c) U" D t" F+ e2 S: v, e, |
If data = "" Then
) G- w j9 t) |3 l( J( x 'debug.Print "Empty cell found: using " & PrevData(r)( B0 _* s2 T( G5 e- Y# P
' use the last valid cell on this row
% x& X8 q. U T8 ~# I) D3 u ' (this is to handle merged cells)% n6 d$ Y& k$ ^. n
data = PrevData(r)
" r( H( ~2 V/ s+ Y1 e End If9 A5 ~4 L! ~, c' W- a+ f
'debug.Print "data:" & data
7 g* s; b( @& K) g2 s( k$ O% \& ~9 j PrevData(r) = data ' save for use by empty cells- A' E: l, p' i$ M3 l
If data = Values(r) Or (data > Values(r) And LE(r) > 0) Or data = "*" Then
$ [# ^2 R7 B: Y0 ]7 z If r = ColHead.Rows.Count Then ' All rows match - It's a go
/ s: i( Y# X) c3 u Match = True4 ~+ P6 S W% z; _+ a# g/ ?2 [# g8 e
End If
" r8 \# }6 `+ l1 U: J7 P! A Else ' This row doesn't match - go to the next column
" D! G! V& A/ p6 c Match = False/ t1 a2 E2 C2 L6 _
Exit For' ^- \- {3 x. a
End If
1 l, c; o }( p A Next r% @- T2 X4 G8 l! D
If Match = True Then ' Don't search any more columns
2 N/ \9 Q' | R5 j6 ^ cindex = c* O9 K8 o0 r5 J. I' m/ F' |
Exit For: d9 F3 G1 h+ A0 `
End If
1 N" J/ E5 `$ f" ?6 N Next c
9 W5 H0 j) r5 m3 Z" B1 E- Y3 ~/ v5 T0 o
If Match = False Then ' Didn't find a matching set of values# L' Q7 w4 u4 _# R0 w
ZZL = "No match for columns"0 y6 _' {2 x0 k0 Q+ L9 P A' o7 F6 G
Exit Function
- e) [ W9 R3 R End If
7 Y! _8 T/ }6 y/ }6 L7 X {( n- e$ {) b) f
cindex = cindex + ColHead.Column - 1
2 `2 p- k6 d9 @( ^, i0 r' ~End If% h* d* P" ^" w" w# `* ]: l
; t8 s: R R6 \: Q
' Return the cell value from Table
8 Q, A6 \" f$ { K6 m( @$ F# X/ m# Z+ J'debug.Print "Answer is in (R,C): " & rindex, cindex
: D* O7 z: r* Y- b t+ {* C* {% M nZZL = ActiveSheet.Cells(rindex, cindex)5 B) c; n& r |
'debug.Print "Answer is : " & ZZL. B* e8 H- d; W
Exit Function
" W" _& W+ A6 e- S
5 h0 f. M5 ?8 {/ ~8 Aerr_handler1:; A% K* B d, Z6 F2 q' C2 b* K! I( A
ZZL = "Error on range '" & rngname & "'"% p8 |! ^ A9 i) V }3 L
6 n: t9 A, K4 s
End Function! M# c& `( v" I
) c) V4 b( [: ~3 Z4 `" W6 _: s
|