Cell Formating with IFERROR using VLookup

M

Mozzz

Board Regular
Joined
May 30, 2011
Messages
66
I am trying to put together the code in MS 360 Excel that will allow me to turn a single cells red if the macro creates an error. Here is my current Code which doesn't work.

Code: 
   Validate that any new accounts

        For i = 6 To FinalRow - 1
            Cells(i, 9).FormulaR1C1 = "=IFERROR(vlookup(RC[-8],Validation_Table,1,False),Cells(2,2).Interior.Color = 192)"
        Next i
 
Last edited by a moderator:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Try:
Code: 
Dim r As Range
For i = 6 To FINALROW - 1
    On Error Resume Next
    Set r = Range("Validation_Table").Resize(, 1).find(what:=Cells(i, 8).Value)
    On Error GoTo 0
    If r Is Nothing Then
        Cells(i, 2).Interior.Color = vbRed
        Set r = Nothing
    End If
Next i
 
Upvote 0
Not sure how that ties in with my vlookup. The Validation Table is nothing more that my current working Trial Balance - I then export my new trial balance out of our Business System but need to make sure all accounts are assigned thus the vlookup against the validatiion table. I am currently getting the N/A when it does find a account on the TB export but since the table is 2000 rows long I have to page down to see the N/A so I would know if there is an account missing. What I am hoping to do is have cells(2,2) turn red if it encounters any error during the validation. So if an account on row 1560 wasn't on the validation table cells(2,2) turns red and I know to go looking. Usually no new accounts are added but when they are I need to catch it. That way I know to go looking instead of having to review all the lines each run for an N/A. I am hoping that I just have a syntax error with the IFERROR I am using. I know that IFERROR gets a little tricky in the needed syntax when it is combined with a vlookup.
 
Upvote 0
Try:
Code: 
Dim r as Range
For i = 6 To FINALROW - 1
    Cells(i, 9).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-8],Validation_Table,1,0))"    
Next i

Set r = Cells(6, 9).Resize(FINALROW - 6).SpecialCells(xlErrors)
If Not r Is Nothing Then 
   Cells(2, 2).Interior.Color = vbRed
   Set r = Nothing
End If
 
Last edited:
Upvote 0
It didn't like the way we have IFERROR setup on your example so I just took the IFERROR out. Then I ran the code. It's giving me the error "Run-Time '1004' Unable to get the SpecialCells Property of the Range class" when it hits Set r = ...
Thanks so much for all the help you have already given but just not quite there yet. Hoping you can help.

Code: 
Sub GetTB()

'       Runs off of InTacct Consolidated Trial Balance - Cut and Paste starting at 2nd Title Row


'      Convert Acccount Text to Numeric


            Columns(1).Select
            With Selection
                .TextToColumns Destination:=ActiveCell.Range("A1")
                .ColumnWidth = 16.86
                .EntireColumn.AutoFit
            End With


'   Get Table Dimensions


                    FINALROW = Cells(Rows.Count, 1).End(xlUp).Row
                    FinalColumn = Cells(5, Columns.Count).End(xlToLeft).Column
                    
                    
'   Validate that any new accounts


        For i = 6 To FINALROW - 1
            Cells(i, 9).FormulaR1C1 = "=vlookup(RC[-8],Validation_Table,1,0)"
        Next i
        
'     Test for errors to ID missing accounts on Validation Table


        Set r = Cells(6, 9).Resize(FINALROW - 6).SpecialCells(xlErrors)
        If Not r Is Nothing Then
           Cells(2, 2).Interior.Color = vbRed
           Set r = Nothing
        End If
        
        
'   Name Table Range for Vlookup


        Range("A5:I" & FINALROW - 1).Name = "CurrentTB"
   
'   Assign which column to update


        mySelection = InputBox("Enter Column Quarter to Post to 1-2-3-4 (5 for Today): ")
        
'   Assign Column on MyData Tab for Vlookup


        Select Case mySelection
        
            Case "1"
                MyColumn = 4
                
                '  Post to MyData Column from Get TB 1st Qtr


                    With Sheets("MyData")
                                MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
                                
                            For i = 2 To MyFinalRow - 1
                                    
                                .Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-2],CurrentTB,8,False),""0"")"
                                .Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
                            Next i
                            
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
                                
                                Application.CutCopyMode = False
                    End With
              
                
            Case "2"
                MyColumn = 5
                '  Post to MyData Column from Get TB 2nd Qtr YTD


                    With Sheets("MyData")
                                MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
                                
                            For i = 2 To MyFinalRow - 1
                                    
                                .Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-3],CurrentTB,8,False),""0"")"
                                .Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
                            Next i
                            
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
                                
                                Application.CutCopyMode = False
                    End With
              
                
            Case "3"
                MyColumn = 6
                '  Post to MyData Column from Get TB 3rd Qtr YTD


                    With Sheets("MyData")
                                MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
                                
                            For i = 2 To MyFinalRow - 1
                                    
                                .Cells(i, MyColumn) = "=IFERROR(vlookup(RC[-4],CurrentTB,8,False),""0"")"
                                .Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
                            Next i
                            
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
                                
                                Application.CutCopyMode = False
                    End With
              
                
            Case "4"
                MyColumn = 7
                '  Post to MyData Column from Get TB 4th Qtr YTD


                    With Sheets("MyData")
                                MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
                                
                            For i = 2 To MyFinalRow - 1
                                    
                                .Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-5],CurrentTB,8,False),""0"")"
                                .Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
                            Next i
                            
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
                                
                                Application.CutCopyMode = False
                    End With
              
                
            Case "5"
                MyColumn = 13
                '  Post to MyData Column from Get TB Today


                    With Sheets("MyData")
                                MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
                                
                            For i = 2 To MyFinalRow - 1
                                    
                                .Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-11],CurrentTB,8,False),""0"")"
                                .Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
                            Next i
                            
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
                                .Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
                                
                                Application.CutCopyMode = False
                    End With
              
            
        End Select
    
   
'  Post to MyData Column from Get TB
   
'    With Sheets("MyData")
'        MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
'
'        For i = 2 To MyFinalRow - 1
'
'        .Cells(i, MyColumn).FormulaR1C1 = "=vlookup(RC[-8],CurrentTB,8,False)"
'
'        Next i
'
'    End With


End Sub


Thx so much

Mozzz
 
Upvote 0
Thank you for all your help! I found the problem. Works perfect now!

Code: 
        Set r = Cells(6, 9).Resize(FINALROW - 6).SpecialCells(xlCellTypeFormulas, 16)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
230
igold
igold
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
219
SBExcel123
S
R
  • Solved
Vlookup Referencing a Dynamic Workbook Returns Error 438
Replies
5
Views
554
Peter_SSs
Peter_SSs
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
518
AlfredDB
A
H
  • Solved
Using Macro/VLOOKUP to return format of cell
Replies
9
Views
1K
mumps
mumps

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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