Mitch Swetsky
asked on
EXCEL VBA How can I add "if(iserror" to the vba code sample (related to Q 36549507)
I need to ask a question related to the solution provided in Q 36549507.
I need to add IF(ISERROR
"=VLOOKUP(RC[-8],P01Curren tmonth.csv !R1C24:R15 8C29,6,FAL SE)"
, 0 ,
"=VLOOKUP(RC[-8],P01Curren tmonth.csv !R1C24:R15 8C29,6,FAL SE)"
But I can't get the syntax correct, could you help?
I need to add IF(ISERROR
"=VLOOKUP(RC[-8],P01Curren
, 0 ,
"=VLOOKUP(RC[-8],P01Curren
But I can't get the syntax correct, could you help?
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
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.
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:
This probably has some typos in it, so check workbook/worksheet names etc.
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
This probably has some typos in it, so check workbook/worksheet names etc.
ASKER
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).S elect
ActiveCell.Offset(0, 1).Select
' ID variable to go back for Fill-Down
Set rngFound = ActiveCell
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).S
ActiveCell.Offset(0, 1).Select
' ID variable to go back for Fill-Down
Set rngFound = ActiveCell
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ASKER
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
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
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.
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.
ASKER
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
Thanks Again
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.
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.
ASKER
Set rngFormula = Range(rngFound, Cells(LastRow, LastCol + 1))
rngFormula.FormulaR1C1 = _
"=VLOOKUP(RC[-8],P01Curren