can concatenated result extract an address ?
Incomplete modification - but don't be afraid to debug it yourself. Any,
see corrections.
Function mergem_con(r As Range, s As String, _
oset as Long) As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = s1 & "," & v
s2 = s2 & "," & va
s3 = s3 & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
mergem_con = s1 & chr(10) & s2 & chr(10) s3
End Function
-----------------
Function mergem_con(r As Range, s As String, oset as Long, Choice as Long)
As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = s1 & "," & v
s2 = s2 & "," & va
s3 = s3 & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
Select Case Choice
Case 1
mergem_con = s1
Case 2
mergem_con = s2
Case 3
mergem_con = s3
End Select
End Function
--
Regards,
Tom Ogilvy
"romelsb" wrote in message
...
Sir Tom,
after test, the incomplete result gives me the last searched value only.
myCase 1 result ",5"
myCase 2 result ",A15"
myCase 3 result ",hht"
regards
--
*****
birds of the same feather flock together..
"Tom Ogilvy" wrote:
To return values, address and value from an offset location (where oset
is
the offset; 1 = column B if r is columnA)
Function mergem_con(r As Range, s As String, oset as Long) As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
mergem_con = s1 & chr(10) & s2 & chr(10) s3
End Function
if you want to choose what to return of the 3 possibilities
Function mergem_con(r As Range, s As String, oset as Long, Choice as
Long)
As String
Dim once As Boolean
Dim s1 as String, s2 as String, s3 as String
Dim v, va, v1
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
va = rr.Address(0,0)
v1 = rr.offset(0,oset).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
s1 = mergem_con & "," & v
s2 = mergem_con & "," & va
s3 = mergem_con & "," & v1
Else
s1 = v
s2 = va
s3 = v1
once = True
End If
End If
End If
Next
Select Case Choice
Case 1
mergem_con = s1
Case 2
mergem_con = s2
Case 3
mergem_con = s3
End Select
End Function
--
Regards,
Tom Ogilvy
"romelsb" wrote in message
...
thanks Sir Tom,
something like this
=mergem_con(A1:A93,"1")
"2, 3, 3,2,2,etc"
and result for the question 1
"A1,A2,A3,A4,A5,etc" which is the address of the first result
and result for the question 2 offset(0,1) text values on the
"abc,bcd,cde,def,efg,etc" which is the content of the cell
offset(0,1)
if not possible maybe another udf something like
= merger(mergem_con,r,c)
offset values (0,1) may be better if not limited to a single row or
column,
some kind of generic offsets(n,m) since the mergem_con function can be
used
on two direction.
best regards
romelsb
then mergem_con returns:
"2, B1,3, B2,3, B3,2, B16,2, B29,3, B30,3, B39,2, B40,3, B42,3, B43,2,
B44,3, B53,3, B61,3, B69,3, B72,2, B75,2, B79,2, B84,2, B87,2, B90,2,
B93"
--
*****
birds of the same feather flock together..
"Tom Ogilvy" wrote:
if I put
=trunc(rand()*8-4)
in A1 and drag fill it down to A93
and in B1 I put
="B"&row()
and drag it down to B92
then in C1 I put
=mergem_con(A1:A93,"1")
then mergem_con returns:
"2, B1,3, B2,3, B3,2, B16,2, B29,3, B30,3, B39,2, B40,3, B42,3, B43,2,
B44,3, B53,3, B61,3, B69,3, B72,2, B75,2, B79,2, B84,2, B87,2, B90,2,
B93"
without the double quotes. (the results are just an example.
Whatever
was
in the cell in column B would be returned.)
So I am not sure what your question is.
--
Regards,
Tom Ogilvy
"romelsb" wrote in message
...
thanks, Sir Tom,
what shall be my formula syntax to grab the adjacent cells or/and
the
search
result address
romelsb the driller
--
*****
birds of the same feather flock together..
"Tom Ogilvy" wrote:
Function mergem_con(r As Range, s As String) As String
Dim once As Boolean
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
v1 = rr.offset(0,1).Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
mergem_con = mergem_con & "," & v & ", " & v1
Else
mergem_con = v & ", " & v1
once = True
End If
End If
End If
Next
End Function
--
Regards,
Tom Ogilvy
"romelsb" wrote in message
...
Hello,Sirs,
i got a very useful smart udf [called mergem_con] from Gary's
Student
as
simple as follows: for pure numbers.
'*************
Function mergem_con(r As Range, s As String) As String
Dim once As Boolean
mergem_con = ""
once = False
For Each rr In r
v = rr.Value
If IsNumeric(v) And Not IsEmpty(v) Then
ss = "=" & v & s
If Evaluate(ss) Then
If once Then
mergem_con = mergem_con & "," & v
Else
mergem_con = v
once = True
End If
End If
End If
Next
End Function
'***************
This can have a result like (without #value error)
on b1=mergem_con(A2:A100,"1")
----
result is 1.2,1.25,1.33,1.5,1.05
which are located A33,A55,A66,A67,A99
based from the above result
i also need to grab the cell contents of the adjacent column B in
same
row
on cells B33,B55,B66,B67,B99.
with result like this ........[ C-33,B-55,S-66,RB-67,FG-99 ]
column B contains pure text only.
is it possible, to extend the UDF to collect the
1. cell address on Column A of the numerical result.
2. corresponding cell content on the adjacent cell(s) Student...
this may seem unusual question, but your unique help will do a
lot
of
magic
for the desired eco-results.
thanks and more power to forum members,
romelsb the driller
--
*****
birds of the same feather flock together..
|