Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

EXCEL VBA  How can I add "if(iserror" to the vba code sample (related to Q 36549507)

Posted on 2011-09-16
12
308 Views
Last Modified: 2012-05-12
I need to ask a question related to the solution provided in Q 36549507.
I need to add IF(ISERROR
 "=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
 , 0 ,
"=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"

 But I can't get the syntax correct, could you help?
0
Comment
Question by:Mswetsky
  • 6
  • 6
12 Comments
 
LVL 1

Author Comment

by:Mswetsky
ID: 36549540
The original solution that I need to amend follows:
   
Set rngFormula = Range(rngFound, Cells(LastRow, LastCol + 1))

rngFormula.FormulaR1C1 = _
                                             "=VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"
0
 
LVL 33

Expert Comment

by:Norie
ID: 36549578
Try this.
strLookup = "VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)" 

strFormula = "=IF(ISERROR(" & strLookup & "), 0, " & strLookup & ")"

Set rngFormula = Range(rngFound, Cells(LastRow, LastCol + 1))

rngFormulaR1C1 = strFormula

Open in new window

You don't need to use the 2 strings, strLookup and strFormula, but I think it kind of makes it a little clearer what's happening.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36549671
I get an error  at Set rngFormula
Attached is the error box Sample of error message
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Norie
ID: 36549756
That isn't anything to do with the formula or the code I posted for it,  the problem is with the code before that.

One thing that appears to be missing from that is setting the range rngFound, which I think was in the original code.

I can't copy the text from the image but try this:
With Workbooks("2011 U.S. Financial Plan.xls").Sheets("Actual & Fixed Workloads")

      LastRow = .Range("A" & Rows.Count).End(xlUp).Row

      LastCol = .Range("B3").End(xlToRight).Column

      Set rngFound = .Cells(LastRow, LastCol)  ' ******************************

       strLookup = "VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)" 

       strFormula = "=IF(ISERROR(" & strLookup & "), 0, " & strLookup & ")"

       Set rngFormula = Range(rngFound, Cells(LastRow, LastCol + 1))

       rngFormulaR1C1 = strFormula

End With

Open in new window


This probably has some typos in it, so check workbook/worksheet names etc.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36550313
First I want to thank you for your time and patience.
I appreciate your expertise!

rngFound should be the top cell in the empty Col not the last ones.

If we look at the original Q  I had some code that set activecell= rngFound and you replaced that section with the current code.

the problem with the code is, it is not getting a value into rngfound with this next statement.
    ' this is the next empty cell, it's the same as ActiveCell in your code
    Set rngFound = Cells(3, LastCol + 1)

The original code follows:---------------
    Windows("2011 U.S. Financial Plan.xls").Activate
    Sheets("Actual & Flexed Workload").Activate
   LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("B3").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
' ID variable to go back for Fill-Down
   Set rngFound = ActiveCell
0
 
LVL 33

Expert Comment

by:Norie
ID: 36550440
That line of code is not meant to put a value in rngFound.

It's the equivalent of your's without the ActiveCell part.

It is the top cell in the next blank column.

The syntax for Cell is Cells(<row>, <column>), so

Cells(3, LastCol+1)

is row 3 and column LastCol+1.

Which if row 3 is the top row is the to cell in the next blank column to the right of the existing data.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36550485
PS

Forgot to ask - did you try the last code I posted?

It does have some mistakes, as I said, but they are mainly typos.

Here's the code, tested and with the errors fixed.
With Workbooks("2011 U.S. Financial Plan.xls").Sheets("Actual & Fixed Workloads")

      LastRow = .Range("A" & Rows.Count).End(xlUp).Row

      LastCol = .Range("B3").End(xlToRight).Column

      Set rngFound = .Cells(3, LastCol + 1) ' ******************************

       strLookup = "VLOOKUP(RC[-8],P01Currentmonth.csv!R1C24:R158C29,6,FALSE)"

       strFormula = "=IF(ISERROR(" & strLookup & "), 0, " & strLookup & ")"

       Set rngFormula = Range(rngFound, Cells(LastRow, LastCol + 1))

       rngFormula.FormulaR1C1 = strFormula

End With

Open in new window

0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36550573
I did try the previous code but I must be confused where it goes.
I tried the latest suggestion and get a subscript out of range at the With Workbooks... error2
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 36550619
In case theis helps, this is the finace sheet showing the months in row 2 and the year to date data filled in (almost).
At the end of my previous code pic you can see that I am tryinig to get back to rngFound so I can use the month label to navigate further with the next steps in the macro.
samp3.JPG
0
 
LVL 33

Expert Comment

by:Norie
ID: 36550646
Could you do something?

Check the names of the worksheets/workbooks in the code?

My eyesight isn't 20/20 and I copied the code from your picture so I may have added an extra character somewhere by mistake.

Might have been an 'a' or  's' or 'd', not sure.

As for getting back to rngFound, it hasn't changed and still refers to the same cell after you've the code has run.
0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 36554889
I took out the With statements since I already had the bookk & sheet selected, The using ther strings helped me to use the if(ISERROR as you showed.

Thanks Again
0
 
LVL 33

Expert Comment

by:Norie
ID: 36554943
Well if that worked that's the important thing.

Just one thing, you can't rely on activating/selecting worksheets and you could end up with the code putting the formulas
on the wrong worksheet.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

860 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