?
Solved

Small vlookup issue

Posted on 2011-04-26
2
Medium Priority
?
229 Views
Last Modified: 2012-05-11
Hello all

I have an issue with a macro.

When i put the macro in the Sheets("Zero Revenue"), the macro works perfectly:
Private Sub CommandButton2_Click()
 
     With Sheets("Zero Revenue").Range([T4], [s10000].End(xlUp).Offset(0, 1))
        .Formula = "=IF(ISNA(VLOOKUP(S4,'3P carrier list'!C:N,12,0)),""NO"",VLOOKUP(S4,'3P carrier list'!C:N,6,0))"
        .Value = .Value
    End With
End Sub

Open in new window


But if i start from another sheet in the same spreadsheet,it does not work, even if i say:
  Sheets("Zero Revenue").select

    Application.ScreenUpdating = True
         
Sheets("Zero Revenue").Activate
 With Sheets("Zero Revenue").Range([T4], [s10000].End(xlUp).Offset(0, 1))
        .Formula = "=IF(ISNA(VLOOKUP(S4,'3P carrier list'!C:N,12,0)),""NO"",VLOOKUP(S4,'3P carrier list'!C:N,6,0))"
        .Value = .Value
    End With

Open in new window


It give me an error on:
With Sheets("Zero Revenue").Range([T4], [s10000].End(xlUp).Offset(0, 1))

Open in new window


Run-time error '1004':
Application-defined or object-defined error

What can i do to fix this please?

Thanks again for your help
0
Comment
Question by:Wilder1626
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35470285
Private Sub CommandButton2_Click()
 
     With Sheets("Zero Revenue")
        with .Range(.[T4], .[s10000].End(xlUp).Offset(0, 1))
        .Formula = "=IF(ISNA(VLOOKUP(S4,'3P carrier list'!C:N,12,0)),""NO"",VLOOKUP(S4,'3P carrier list'!C:N,6,0))"
        .Value = .Value
       end with
    End With
End Sub

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 35470328
Thanks, this is great.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question