Vlookup where value begins with

[Stephen]

I have a nother vlookup problem where I'm hoping to be able to return a value
if the lookup value BEGINS with a certain number which can be found in a
particular cell... I'm not sure it's going to be possible to say...

If the lookup values in the named range begin with the value found in cell
x2, then return the value from the third column in the namged range.

thanks in advance.

 

[sebastienm]

Hi,
What about somthing like:

If A2 starts with X2 then VLOOKUP(...) else A2
= IF( LEFT(A2,LEN(X2)) = LEFT(X2,LEN(X2)) , VLOOKUP(...) , A2 )

 

[Stephen]

Thanks for the reply but nope that didn't do it. I don't think I'm
explaining myself properly...

My first sheet (sheet1) has a value in cell x32. I want to look for cells
that begin with that value in my second sheet (sheet2) in column D. Once a
value is found in column d of my second sheet i want that found value to be
copied to sheet1 cell x33.

Then keep looking for values (in sheet2 column D) that begin with the value
in sheet1 cell x32 and copy the matching results downward in cells x34, x35
etc... I guess it's sort of like and if then with a next thrown in there for
good measure.

Hope this helps make things clearer.

Thanks!

 

[sebastienm]

ok, i get it now.

Finding a single match
-------------------------
A nice feature of the VLOOKUP function is that it takes wildcard characters.
That is, in cell x33, you can write:

=VLOOKUP( x32 & "*" , sheet2!D, 1, False)

Notes:
- x32 & "*" means: search for values starting with value in x32 (wildcard
* = any character)
- sheet2!D is fixed so only the first match will be found
- if no match then #N/A is returned

Finding All Matches
---------------------

You could probably use an array formula to detrmine all matches but maybe a
better way would be to use a macro instead of a function:

Sub CopyMatches()
dim rg as range

'''' find matching cells using wildcard
set rg = FindAllBegin sheet1.range("x32").value & "*" , sheet2.range("d")

''' copy matching cells over
if not rg is nothing then
rg.copy sheet1.Range("x33")
end if

End sub

Function FindAll (What as Variant, Where as Range) as range

Dim cell As Range, rgResult As Range
Dim firstAddress As String

With Where
Set cell = .Find(What, LookIn:=xlValues, lookat:=xlWhole)

If Not cell Is Nothing Then
firstAddress = cell.Address
Do
If rgResult Is Nothing Then
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> firstAddress
End If
End With

Set FIndAll = rgResult
end function

 

[Stephen]

I see where this is going but I'm getting an error when I'm getting a syntax
error when I tweek it for my specifics... Your ogirnal code has the sub
reference the function of FindAllBegin where the Function is named FindAll,
which is why I changed that thinking that was the problem but it did not fix
it. Here is my modification...

Sub CopyMatches()
Dim rg As Range

'''' find matching cells using wildcard
Set rg = FindAll January.Range("X31").Value & "*",
weights.xls!CustomerAccounts.Range("D")

''' copy matching cells over
If Not rg Is Nothing Then
rg.Copy January.Range("X33")
End If

End Sub

Function FindAll(What As Variant, Where As Range) As Range

Dim cell As Range, rgResult As Range
Dim firstAddress As String

With Where
Set cell = .Find(What, LookIn:=xlValues, lookat:=xlWhole)

If Not cell Is Nothing Then
firstAddress = cell.Address
Do
If rgResult Is Nothing Then
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> firstAddress
End If
End With

Set FindAll = rgResult
End Function

 

[sebastienm]

change
Set rg = FindAll( January.Range("X31").Value & "*", weights.xls!
CustomerAccounts.Range ("D")
with
''' ----------------------------------------
Set rg = FindAll( _
ThisWorkbook.Worksheets("January").Range("X31").Value & "*" _
, Workbooks("weights.xls").Worksheets("CustomerAccounts").Range("D") _
)
''' ----------------------------------------

 

[Stephen]

gives me a runtime error '9' - Subscript out of range? - ?(rg=nothing)?

Set rg = FindAll(ThisWorkbook.Worksheets("January").Range("$X$31").Value &
"*", Workbooks("weights.xls").Worksheets("CustomerAccounts").Range("D2300"))

 

[sebastienm]

ok, there is something that vba doesn't find.
Try debug.print on the following expressions
? ThisWorkbook.Worksheets("January").name
? ThisWorkbook.Worksheets("January").Range("$X$31").Value
? Workbooks("weights.xls").name
? Workbooks("weights.xls").Worksheets("CustomerAccounts").name
Anything inconsistent?
Also try running step-by-step (key F8) to see if the error is in the calling
line or within the function.

 

[Stephen]

Debug.Print ThisWorkbook.Worksheets("January").Name; ""
Debug.Print ThisWorkbook.Worksheets("January").Range("$X$31").Value
Debug.Print Workbooks("weights.xls").Name
Debug.Print Workbooks("weights.xls").Worksheets("CustomerAccounts").Name

All returns the expected BUT when I add...

Debug.Print
Workbooks("weights.xls").Worksheets("CustomerAccounts").Range("D2300").Value

I get a type mismatch error type 13. - significant?

 

[sebastienm]

error type 13 - yes, that's normal, 'cause it's an array so debug.print
cannot write it.
....
Have you tried running step-by-step (key F8)... (or putting a breakpoint at
the beginning of the FindAll function) to see if it even goes there, because
depending on the vba editor setting, the error could come from deeper but
shows/errorsOut in the calling procedure.

 

[Stephen]

I tried the F8 and it din't give me any firther clue and when I breakpoint at
the beginning of the function it doens't look like it's getting to it. I
think the problem is in the calling line. From what I'm reading on the typ
mismatch error I'm thinking it chokes on the range "D".

 

[sebastienm]

.... must be something simple that we are missing...

First,
----------
the code works on my side, i just checked.

Second,
----------
what if you replace
...Range("x31").value
by
...Range("x31").text

Third ,
--------
in a new blank book, try my code (even with no data, just to see whether or
not it breaks:
''' ------------------------------
Sub CopyMatches()
Dim rg As Range

'''' find matching cells using wildcard
Set rg = FindAll( _
Sheet1.Range("x32").Value & "*" _
, Sheet2.Range("d"))

''' copy matching cells over
If Not rg Is Nothing Then
rg.Copy Sheet1.Range("x33")
End If

End Sub

Function FindAll(What As Variant, Where As Range) As Range

Dim cell As Range, rgResult As Range
Dim firstAddress As String

With Where
Set cell = .Find(What, LookIn:=xlValues, lookat:=xlWhole)

If Not cell Is Nothing Then
firstAddress = cell.Address
Do
If rgResult Is Nothing Then
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> firstAddress
End If
End With

Set FindAll = rgResult
End Function
''' -------------------------------------------------------

 

[Stephen]

Got it. I followed your suggestions and ran your original code in a new
workbook. As it turns out the error was in reference to the external
workbook where my sheet2 was residing. By moving that sheet into my first
workbook and keeping it hidden I can run your code (works great thank you!)
and keep my other functions and code intact.

Thanks again for all your help, you've been a patient scholar.

 

[sebastienm]

No problem Stephen. Glad I could help.