VBA Returning a Range

[Blammdon1]

I can't find this anywhere on the net and it may sound stupid but, how do i return an actual range from a user created function so that another function say the MIN function can use that value to look at that range. (I mean like an actual Range, one that turns blue in excel not the values in that range)
Example:
******************************************************
Public Function Example(Column1 as String, Row1 as Integer, Column2 as String, Row2 as Integer )

Example= ?????? 'What should this return be?


End Function

Then use this in the min function
MIN(Test("G",3,"G",7)
It should return the minimum value in the Range "G3:G7"

Thanks in advance

 

[iliace]

Public Function Test(c1 As String, r1 As Long, c2 As String, r2 As Long) As Excel.Range
  Set Test = Range(c1 & r1 & ":" & c2 & r2)
End Function

 

[Blammdon1]

yeah that was what i had at first, but it only returns the values in that range. I want it to return the actual range,
Example:
Test("G",1,"G",7) would have to return the usable range G1:G7 not the values of that range

 

[Blammdon1]

Please help!

 

[iliace]

It returns an array, which for all intents and purposes is like a range. So if you said, for instance:

=MATCH(G4,Test("G",1,"G",7),0)

you would get a result of 4. It works for MIN as well. If you calculate just the Test() portion, you would get the same result as you would if you calculated G1:G7. So what are you trying to do that this is not useable?

 

[Blammdon1]

Yeah i know what you mean...I thought it would work first (the way you had it) but say i want to use this function to do a COUNTIF with the range i was wanting to use being the range i got from the Test() function. I would have to have it return the range and not the values.

 

[iliace]

I have some numbers in column H right now. Three of them are negative. This formula:

=COUNTIF(Test("h",4,"h",25),"<0")

returns 3, which is correct. Now I add 1, 10, and 100 in the column just to the right, next to each of the negative numbers. This formula:

=SUMIF(Test("h",4,"h",25),"<0",I4:I25)

returns 111, which is correct. So does this one:

=SUMIF(Test("h",4,"h",25),"<0",Test("i",4,"i",25))

 

[Kenneth Hobson]

Return the type as variant.

Public Function Test(c1 As String, r1 As Long, c2 As String, r2 As Long) As Variant
  Set Test = Range(c1 & r1 & ":" & c2 & r2)
End Function

 

[Blammdon1]

Ok i dont know what the deal is, i pasted your function into a new module and i pasted the values (with 3 being negative) into row H and then put the COUNTIF function in another cell and i still get the same #VALUE!
I would really like it if there was just a way to show the actual range and not it values but i'm ok with your idea now, but as of now that doesn't even work.

 

[Blammdon1]

Return the type as variant.

Public Function Test(c1 As String, r1 As Long, c2 As String, r2 As Long) As Variant
  Set Test = Range(c1 & r1 & ":" & c2 & r2)
End Function

Yeah i tried that earlier that just gives me the same problem by returning just the values and not the actual range Ex: "A1:A7". Ah well i'm giving up for weekend.