Hi Greg. Thanks for your help:)
It helps me to continuing develop my function. Now I have some more
elaborated (obviously can't it be made without your help). I don't knew
how to retrieve the range by cols and rows until I see your code and
studied it and the help of Excel.
Let me show you and tell me what do you think about it.
Option Explicit
Option Base 1
Function ExtractIf(SearchRng As Range, cSearchVal As String, nSearchCol
As Integer, nReturnCol As Integer) As String
' Rango de búsqueda, Dato a buscar, Columna donde se buscará, Columna
que se regresará
' Propiedades importantes de los rangos:
' SearchRng.Columns.Count = Columnas del rango
' SearchRng.Rows.Count = Filas del rango
' SearchRng.Count = Celdas del rango (Columns * Rows)
' Declaracion de variables
Dim nItem As Integer ' Ciclos
Dim nRows As Integer ' Filas del rango
Dim nCols As Integer ' Columnas del rango
Dim cTxt As String ' Texto a devolver separado por
comas
' Inicialización de variables
nRows = SearchRng.Rows.Count ' Número de filas
nCols = SearchRng.Columns.Count ' Número de columnas
cTxt = "" ' Resultado obtenido
' Depuración de parámetros
' ToDo: What happens with date values in cSearchVal? If Not
IsNumeric(nSearchCol) Then nSearchCol = 1
If Not IsNumeric(nReturnCol) Then nSearchCol = nCols
If nSearchCol nCols Then nSearchCol = 1
If nReturnCol nCols Then nReturnCol = nCols
' Busca en el rango SearchRng el valor cSearchVal en la columna
nSearchCol
' Si encuentra una coincidencia, devuelve el valor de la misma fila
pero en
' la columna nReturnCol
For nItem = 1 To nRows
If cSearchVal = SearchRng(nItem, nSearchCol).Value Then
cTxt = cTxt & SearchRng(nItem, nReturnCol).Value & ", "
End If
Next
' Elimina la última coma cuando sea necesario
If Len(cTxt) 0 Then
cTxt = Left(cTxt, Len(cTxt) - 2)
End If
' Regresa la lista de los valores obtenidos
' ToDo: How to return an array instead of a String?
' ToDo: How to return the data so it can be used as a
' so it can be displayed in the dropdown listbox of a
' Cell:Data:Validation? ExtractIf = cTxt
End Function
--
eddy56
------------------------------------------------------------------------
eddy56's Profile:
http://www.excelforum.com/member.php...o&userid=37006
View this thread:
http://www.excelforum.com/showthread...hreadid=567294