Returning 2 values with an =absent function

[jacinthn]

i found this absent function that searces through all the sheets of a workbook and finds a match and returns the value from column 5
however ive been trying to modify the
absent = Sheets(sht).Cells(c.Row, 5) line to return both column 5 and 6 but to put the column 6 value in the column next to the column where the =Absent(A1) formula is in the spreadsheet
ive tried
absent = Sheets(sht).Cells(c.Row, 5, ActiveCell.Offset(0, 1).Cells(c.Row, 6
absent = Sheets(sht).Cells(c.Row, 5, ActiveCell.Offset(0, 1), 6)
absent = Sheets(sht).Cells(c.Row, 5, 6)
nothing is working it works great returning the value from column 5 but how can i get the value from column 6 also to be added into the adjacent cell, please help!!! :'(

Option Explicit
Function absent(ByVal find_name As Range)
Dim sht As Integer, c As Variant
Application.Volatile
'Loop through sheets
For sht = 2 To Sheets.Count
'"Lookup" Value from argument cell in each sheet
With Sheets(sht).Columns(2).Cells
Set c = .Find(find_name)
If Not c Is Nothing Then
absent = Sheets(sht).Cells(c.Row, 5)
End If
End With
Next
End Function

 

[Ruddles]

A function can't put anything in a worksheet cell, it can only return a value which you place in the cell by calling the function from a worksheet formula. So you can't put anything in the adjacent cell from within the function.

However you can tell the function to return a value from whichever column you want by passing the column number to it when you call it and asking it to use that column - whatever it is, instead of always 5 - by putting the formula =absent("somename",5) in the instead of just =absent("somename").

Am I explaining this clearly enough?

First of all you need to change your function to receive and use the column number, as follows:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Function absent(ByVal find_name As Range[COLOR=red][B], byval use_column as long[/B][/COLOR])[/FONT]
 
[FONT=Fixedsys]  Dim sht As Integer, c As Variant[/FONT]
 
[FONT=Fixedsys]  Application.Volatile[/FONT]
 
[FONT=Fixedsys]  'Loop through sheets[/FONT]
[FONT=Fixedsys]  For sht = 2 To Sheets.Count[/FONT]
[FONT=Fixedsys]    '"Lookup" Value from argument cell in each sheet[/FONT]
[FONT=Fixedsys]    With Sheets(sht).Columns(2).Cells[/FONT]
[FONT=Fixedsys]      Set c = .Find(find_name)[/FONT]
[FONT=Fixedsys]      If Not c Is Nothing Then[/FONT]
[FONT=Fixedsys]        absent = Sheets(sht).Cells(c.Row, [B][COLOR=#ff0000]use_column[/COLOR][/B])[/FONT]
[FONT=Fixedsys]      End If[/FONT]
[FONT=Fixedsys]    End With[/FONT]
[FONT=Fixedsys]  Next[/FONT]
 
[FONT=Fixedsys]End Function[/FONT]

Then you have to change your formulae from =absent("somename") to =absent("somename",5) when you want to search in column 5 or =absent("somename",6) when you want to search in column 6.

Let me know if you can't get this to work.

 

[mikerickson]

Functions do not put values in cells.

Functions evaluate arguments and then return values. It is not the function that puts a value in a cell, it is the user who puts the function in the cell which then shows the calculated value.

What you could do for your situation is to include a "column_returned" argument to your function.

Function absent(ByVal find_name As Range, Optional column_returned as Long)
    Dim sht As Integer, c As Variant

    If column_returned < 1 Then column_returned = 5: Rem default value
    Application.Volatile
    'Loop through sheets
    For sht = 2 To Sheets.Count
    ' "Lookup" Value from argument cell in each sheet

        With Sheets(sht).Columns(2).Cells
            Set c = .Find(find_name)
            If Not c Is Nothing Then
                absent = Sheets(sht).Cells(c.Row, column_returned)
            End If
        End With
    Next
End Function

and put =Absent(A1, 5) in one cell and =Absent(A1, 6) in the cell to its right.

 

[Ruddles]

 

[jacinthn]

Brilliant!!!!!!!!!!!!!!! worked perfectly THANKS SO MUCH!!!!!!!!!!!!!!!! i spent 3 hours on friday trying to figure that out with no luck, you are a LIFE SAVER THANK YOU SO VERY MUCH!!!

 

[jacinthn]

do either of you have any idea why when i use either of the 2 above formulas when i put a value in any of the worksheets columns 5 or 6 intitially it is returned to the main page with the formula but then excel automatically recalculates right away and fills both columns with #Value! so all the formula resilts show #value!
so the columns with the =absent(a1,5) doesnt show any value... its been doing this all day and i cant figure out why because it wasnt doing that with the original function just the modified version
Option Explicit
Function absent(ByVal find_name As Range, ByVal use_column As Long)
Dim sht As Integer, c As Variant
Application.Volatile
'Loop through sheets
For sht = 2 To Sheets.Count
'"Lookup" Value from argument cell in each sheet
With Sheets(sht).Columns(1).Cells
Set c = .Find(find_name)
If Not c Is Nothing Then
absent = Sheets(sht).Cells(c.Row, use_column)
End If
End With
Next

End Function

and


Function absent(ByVal find_name As Range, Optional column_returned as Long)
Dim sht As Integer, c As Variant

If column_returned < 1 Then column_returned = 5: Rem default value
Application.Volatile
'Loop through sheets
For sht = 2 To Sheets.Count
' "Lookup" Value from argument cell in each sheet

With Sheets(sht).Columns(2).Cells
Set c = .Find(find_name)
If Not c Is Nothing Then
absent = Sheets(sht).Cells(c.Row, column_returned)
End If
End With
Next
End Function

both do the same thing i get the columns filled with #Value

 

[Ruddles]

It seems to work okay here.

 

[Ruddles]

PS. Please can you post code inside code tags - the # icon in the advanced editor toolbar? This will use a fixed-width font to display the code and also preserves indenting, both of which assist others to read and understand your code and encourages them to respond.

 

[jacinthn]

thanks will note for future i figured it out, turned off application screen updating so it wont change while its calculating :D thanks again