can concatenated result extract an address ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
thanks Sir Tom,
something like this
=mergem_con(A1:A93,">1")
Click to expand...

"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:
Click to expand...
"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 said:
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 said:
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
thanks Sir Tom,
something like this
=mergem_con(A1:A93,">1")
Click to expand...

"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:
Click to expand...
"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 said:
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 said:
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..



:

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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
thanks Sir Tom,
something like this
=mergem_con(A1:A93,">1")
Click to expand...

"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:
Click to expand...
"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 said:
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

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..



:

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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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..



:

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

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..



:

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


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
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a UDF within SUMPRODUCT 10
Creating UDF prompts (XL 2007) 3
Modify code to insert borders 2
UDF returns different result in worksheet to that in VBA 6
Concatenating two spreadsheets by a column 1
Statement to Automate new sheets if is needed it. 3
Creating a UDF from a VBA routine... 5
Loop Without Do 4

Back
Top