Can't select VLOOKUP result values after replacing #N/A

S

spidaman

Board Regular
Joined
Jul 26, 2015
Messages
116
Office Version
  1. 365
Platform
  1. Windows
I'm trying to vlookup values in 3 worksheets in workbook wbPI against a table in second workbook wbPA. I am then replacing the #N/As and applying formatting to the positive look up results. Can anyone see why the code isn't working after the second vlookup please?

Code: 
Sub LookUp_View()


Dim PAccept1 As Long, PInterv1 As Long, PInterv2 As Long, PInterv3 As Long
Dim wbPA As Workbook, wbPI As Workbook
Dim wsPA1 As Worksheet, wsPI1 As Worksheet, wsPI2 As Worksheet, wsPI3 As Worksheet
Dim PARang As Range


    Set wbPA = Workbooks("Book 2")
    Set wsPA1 = wbPA.Sheets(1)
    Set wbPI = Workbooks("Book 1")
    Set wsPI1 = wbPI.Sheets(1)
    Set wsPI2 = wbPI.Sheets(2)
    Set wsPI3 = wbPI.Sheets(3)
    
    PInterv1 = wsPI1.Range("A" & Rows.Count).End(xlUp).Row
    PInterv2 = wsPI2.Range("A" & Rows.Count).End(xlUp).Row
    PInterv3 = wsPI3.Range("A" & Rows.Count).End(xlUp).Row
    PAccept1 = wsPA1.Range("D" & Rows.Count).End(xlUp).Row
    
    Set PARang = wsPA1.Columns("G:H")

    
            wbPA.Activate
            
            wsPA1.Range("H:H").Insert
             
            Range("H1:H" & PAccept1).Value = "YES"
            
            Range("G:H").NumberFormat = "General"
            
                Cells.Select
                Range(Cells.Address).Sort Key1:=Range("G1"), Order1:=xlAscending, Header:= _
                xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal

                wbPI.Activate
            
                wsPI1.Range("B:C").NumberFormat = "General"
                wsPI2.Range("B:C").NumberFormat = "General"
                wsPI3.Range("B:C").NumberFormat = "General"
                
                wsPI1.Range("C2:C" & PInterv1) = Application.VLookup(wsPI1.Range("B2:B" & PInterv1), PARang, 2, False)
                
                        With wsPI1.Range("C2:C" & PInterv1)
                            .Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                             ReplaceFormat:=False
                            .Borders.LineStyle = xlContinuous
                        End With
                        
                        wsPI1.Range("C2:C" & PInterv1).SpecialCells(xlCellTypeConstants, 23).Select
                        
                        With Selection
                            .Interior.ColorIndex = 3
                            .Font.Bold = True
                            .Font.ColorIndex = 1
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                        End With
                    
    
                wsPI2.Range("C2:C" & PInterv2) = Application.VLookup(wsPI2.Range("B2:B" & PInterv2), PARang, 2, False)
                    
                        With wsPI2.Range("C2:C" & PInterv2)
                            .Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            ReplaceFormat:=False
                            .Borders.LineStyle = xlContinuous
                        End With
    
' problem here!!
' debugger highlights next row:


                        wsPI2.Range("C2:C" & PInterv2).SpecialCells(xlCellTypeConstants, 23).Select


                        With Selection
                            .Interior.ColorIndex = 45
                            .Font.Bold = True
                            .Font.ColorIndex = 1
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                        End With


                wsPI3.Range("C2:C" & PInterv3) = Application.VLookup(wsPI3.Range("B2:B" & PInterv3), PARang, 2, False)
                
                        With wsPI3.Range("C2:C" & PInterv3)
                            .Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            ReplaceFormat:=False
                            .Borders.LineStyle = xlContinuous
                        End With
    
                        wsPI3.Range("C2:C" & PInterv3).SpecialCells(xlCellTypeConstants, 23).Select


                        With Selection
                            .Interior.ColorIndex = 10
                            .Font.Bold = True
                            .Font.ColorIndex = 1
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                        End With
                        


End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
The VLOOKUPs do not return an array of values for multiple cells with this syntax.

Code: 
wsPI1.Range("C2:C" & PInterv1) = Application.VLookup(wsPI1.Range("B2:B" & PInterv1), PARang, 2, False)

Try something like this...

Code: 
[color=darkblue]With[/color] wsPI1.Range("C2:C" & PInterv1)
    .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"
    .Value = .Value
    .Borders.LineStyle = xlContinuous
    [color=darkblue]With[/color] .SpecialCells(xlCellTypeConstants, 23)
        .Interior.ColorIndex = 3
        .Font.Bold = [color=darkblue]True[/color]
        .Font.ColorIndex = 1
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] With
 
Upvote 0
Many thanks AlphaFrog that works perfectly.

The VLOOKUPs do not return an array of values for multiple cells with this syntax.
Click to expand...

How come the vlookup seemed to work with that syntax for the first and second sheets?
Code: 
[COLOR=#333333]wsPI1.Range("C2:C" & PInterv1) = Application.VLookup(wsPI1.Range("B2:B" & PInterv1), PARang, 2, False)[/COLOR]

Also I don't understand how the #N/A errors are identified in
Code: 
[COLOR=#333333]    .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"[/COLOR]
?

Sorry Alpha just trying to understand your syntax. Haven't seen
Code: 
IFERROR
function before??
 
Upvote 0
spidaman said:
Many thanks AlphaFrog that works perfectly.



How come the vlookup seemed to work with that syntax for the first and second sheets?
Code: 
[COLOR=#333333]wsPI1.Range("C2:C" & PInterv1) = Application.VLookup(wsPI1.Range("B2:B" & PInterv1), PARang, 2, False)[/COLOR]
Click to expand...

It may not have errored, but I don't think you got the correct results.

Also I don't understand how the #N/A errors are identified in
Code: 
[COLOR=#333333]    .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"[/COLOR]

Sorry Alpha just trying to understand your syntax. Haven't seen IFERROR function before??
Click to expand...

It's a worksheet function.
Excel IFERROR Function
 
Last edited:
Upvote 0
Thanks for your help. That's a useful function I will definitely be using in the future.
 
Upvote 0
AlphaFrog said:
The VLOOKUPs do not return an array of values for multiple cells with this syntax.

Code: 
wsPI1.Range("C2:C" & PInterv1) = Application.VLookup(wsPI1.Range("B2:B" & PInterv1), PARang, 2, False)

Try something like this...

Code: 
[COLOR=darkblue]With[/COLOR] wsPI1.Range("C2:C" & PInterv1)
    .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"
    .Value = .Value
    .Borders.LineStyle = xlContinuous
    [COLOR=darkblue]With[/COLOR] .SpecialCells(xlCellTypeConstants, 23)
        .Interior.ColorIndex = 3
        .Font.Bold = [COLOR=darkblue]True[/COLOR]
        .Font.ColorIndex = 1
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] With
Click to expand...

Sorry to bother you again AlphaFrog but have found that when there are no "YES" results at all from the VLOOKUP it throws up an error. I understand this is because I haven't included an option for there being no SpecialCells(xlCellTypeConstants, 23) at all. But I am struggling to adapt the code to include an IF clause that works. I also need to include a message box that states no values were found in this case. Any chance you could help out with this?

I managed to make it work using a different method but it isn't very economical at all and it isn't formatting some of the "YES" cells the way I'd like it to:

Code: 
Sub My_LookUpPlayers()


Dim PAccept1 As Long, PInterv1 As Long, PInterv2 As Long, PInterv3 As Long
Dim wbPA As Workbook, wbPI As Workbook
Dim wsPA1 As Worksheet, wsPI1 As Worksheet, wsPI2 As Worksheet, wsPI3 As Worksheet
Dim PARang As Range


If MsgBox("Make sure you have 'Second Spreadsheet' Spreadsheet open before running macro" & vbCrLf & "" & vbCrLf & "Click OK to Continue" & vbCrLf & "" & vbCrLf & "Click CANCEL if you still need to open 'Second Spreadsheet'", vbOKCancel, "Look Up Players") = vbCancel Then Exit Sub


    Set wbPA = Workbooks("Second Spreadsheet")
    Set wsPA1 = wbPA.Sheets(1)
    Set wbPI = Workbooks("First Spreadsheet")
    Set wsPI1 = wbPI.Sheets(1)
    Set wsPI2 = wbPI.Sheets(2)
    Set wsPI3 = wbPI.Sheets(3)
    
    PInterv1 = wsPI1.Range("A" & Rows.Count).End(xlUp).Row
    PInterv2 = wsPI2.Range("A" & Rows.Count).End(xlUp).Row
    PInterv3 = wsPI3.Range("A" & Rows.Count).End(xlUp).Row
    PAccept1 = wsPA1.Range("D" & Rows.Count).End(xlUp).Row
    
    Set PARang = wsPA1.Columns("G:H")


            wbPA.Activate
            
            wsPA1.Range("H:H").Insert
             
            Range("H1:H" & PAccept1).Value = "YES"
            
            Range("G:H").NumberFormat = "General"
            
                Cells.Select
                Range(Cells.Address).Sort Key1:=Range("G1"), Order1:=xlAscending, Header:= _
                xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal


                wbPI.Activate
            
                wsPI1.Range("B:C").NumberFormat = "General"
                wsPI2.Range("B:C").NumberFormat = "General"
                wsPI3.Range("B:C").NumberFormat = "General"
                
                        With wsPI1.Range("C2:C" & PInterv1)
                            .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"
                            .Value = .Value
                            .Borders.LineStyle = xlContinuous
                        End With
                        
                        
                        With wsPI2.Range("C2:C" & PInterv2)
                            .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"
                            .Value = .Value
                            .Borders.LineStyle = xlContinuous
                        End With
                        


                        With wsPI3.Range("C2:C" & PInterv3)
                            .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"
                            .Value = .Value
                            .Borders.LineStyle = xlContinuous
                        End With
                        
                      
Dim oRange As Range, foundCell As Range
Dim FirstFoundCell As String, SearchString As String, FoundAt As String


    SearchString = "YES"
    Set oRange = wsPI1.Columns("C:C")
    
    Set foundCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=False, SearchFormat:=False)
    
    If Not foundCell Is Nothing Then
        
        Do Until foundCell Is Nothing
            LastFoundCell = foundCell.Address
           
                            With foundCell
                                .Interior.ColorIndex = 3
                                .Font.Bold = True
                                .Font.ColorIndex = 1
                                .HorizontalAlignment = xlCenter
                                .VerticalAlignment = xlCenter
                            End With


            Set foundCell = oRange.FindNext
            
            If Not foundCell Is Nothing Then If foundCell.Address = LastFoundCell Then Set foundCell = Nothing
        Loop
        
    End If
    
    SearchString = "YES"
    Set oRange = wsPI2.Columns("C:C")
    
    Set foundCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=False, SearchFormat:=False)


        If Not foundCell Is Nothing Then
        
            Do Until foundCell Is Nothing
                LastFoundCell = foundCell.Address
               
                                With foundCell
                                    .Interior.ColorIndex = 3
                                    .Font.Bold = True
                                    .Font.ColorIndex = 1
                                    .HorizontalAlignment = xlCenter
                                    .VerticalAlignment = xlCenter
                                End With
    
                Set foundCell = oRange.FindNext
                
                If Not foundCell Is Nothing Then If foundCell.Address = LastFoundCell Then Set foundCell = Nothing
            Loop
        
        End If




    SearchString = "YES"
    Set oRange = wsPI3.Columns("C:C")
    
    Set foundCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=False, SearchFormat:=False)


        If Not foundCell Is Nothing Then
        
            Do Until foundCell Is Nothing
                LastFoundCell = foundCell.Address
               
                                With foundCell
                                    .Interior.ColorIndex = 3
                                    .Font.Bold = True
                                    .Font.ColorIndex = 1
                                    .HorizontalAlignment = xlCenter
                                    .VerticalAlignment = xlCenter
                                End With
    
                Set foundCell = oRange.FindNext
                
                If Not foundCell Is Nothing Then If foundCell.Address = LastFoundCell Then Set foundCell = Nothing
            Loop
        
        End If


                
End Sub
 
Last edited:
Upvote 0
Try something like this (not tested).

Code: 
[color=darkblue]Sub[/color] My_LookUpPlayers()
    
    [color=darkblue]Dim[/color] ws        [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] PARang    [color=darkblue]As[/color] Range
    
    [color=darkblue]If[/color] MsgBox("Make sure you have 'Second Spreadsheet' Spreadsheet open before running macro" & _
              vbLf & vbLf & "Click OK to Continue" & vbLf & vbLf & _
              "Click CANCEL if you still need to open 'Second Spreadsheet'", _
              vbOKCancel, "Look Up Players") = vbCancel Then Exit Sub
    
    [color=darkblue]With[/color] Workbooks("Second Spreadsheet").Sheets(1)
        [color=green]'.Parent.Activate[/color]
        .Range("H:H").Insert
        .Range("H1:H" & .Range("D" & Rows.Count).End(xlUp).Row).Value = "YES"
        .Range("G:H").NumberFormat = "General"
        [color=darkblue]Set[/color] PARang = .Columns("G:H")
        .UsedRange.Sort Key1:=.Range("G1"), Order1:=xlAscending, Header:=xlNo, _
                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] Workbooks("First Spreadsheet")
        [color=green]'.Activate[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] .Sheets(Array(1, 2, 3))
            ws.Range("B:C").NumberFormat = "General"
            [color=darkblue]With[/color] ws.Range("C2:C" & ws.Range("A" & Rows.Count).End(xlUp).Row)
                .Formula = "=IFERROR(VLOOKUP($B2, " & PARang.Address(1, 1, External:=True) & ", 2, False),"""")"
                .Value = .Value
                .Borders.LineStyle = xlContinuous
                
                [color=darkblue]If[/color] [color=darkblue]Not[/color] .Find("YES", , xlValues, xlWhole, 1, 1, 0) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                    [color=darkblue]With[/color] .SpecialCells(xlCellTypeConstants, xlTextValues)
                        .Interior.ColorIndex = 3
                        .Font.Bold = [color=darkblue]True[/color]
                        .Font.ColorIndex = 1
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                    [color=darkblue]End[/color] [color=darkblue]With[/color]
                [color=darkblue]Else[/color]
                    MsgBox ws.Name, vbExclamation, "No Values Found In This Case"
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]Next[/color] ws
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Thanks very much that looks promising - I'll give it try in the morning. Not too familiar with using arrays tbh so good opportunity to learn about them.
 
Upvote 0
Thanks Alphafrog can confirm that this appears to work perfectly! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Problem with formatting VLOOKUP results across multiple tabs
Replies
0
Views
418
spidaman
S
M
  • Question
Different results running same macro with same data
Replies
3
Views
2K
Logit
Logit
I
  • Question
Selection change event code now to be used on change event code issue
2
Replies
16
Views
809
ipbr21054
I
E
  • Question
Cannot Find Missing Statement Closing
Replies
4
Views
728
erickamiller143
E
H
  • Question
Issues with Excel 2013
Replies
0
Views
3K
Hawk3116
H

Forum statistics

Threads
1,216,075
Messages
6,128,665
Members
449,462
Latest member
Chislobog

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top