iferror statement for macros in vba excel

excel vba loops if-statement

48,643

I am very confused by your question and have a strong feeling there is a MUCH better solution to your problem but I am unsure what exactly your problem is, so here is a working version of your code:

For each cl in range("C1:C200")  
    If IsError(cl) and cl.offset(0, 1).value = "yes" then    
        cl.value = cl.offset(0, -1)
    End If
Next cl

Or this might be more what you want: only look at the errors and use the value to the left if the value to the right is yes?

Sub error()

Dim rngErrors As Range

On Error Resume Next

Set rngErrors = Range("C1:C200").SpecialCells(xlCellTypeFormulas, xlErrors)

For Each rngcError In rngErrors
     If cl.offset(0, 1).value = "yes" then    
        cl.value = cl.offset(0, -1)
    End If
Next rngcError

On Error GoTo 0

End Sub

there is no need for your original else as if the If statement is not met nothing will happen to it anyway. Unless there is a formula in the cell you would like to get rid of.

48,643

Author by

Sean Connecticut

Updated on July 08, 2022

Comments

Sean Connecticut almost 2 years
I have a macro that at one point creates a pivot table. My problem is sometimes not all the values are in the second column and I get an error.

I figured a way around this by getting the values from the column to the left which are the same. But with this method if the pivots are the same I get an "error no special cells found".

Sorry let me be more clear I have a column A for contract names and column C is a pivot that brings up the values of the contract from another table. Where I am getting the error is column B which has an if statement which basically says if column C is "yes" then use contract names. I still want the value if my if statement in column B produces an error

What I want is something along the lines of
```
For each cl in range("C1:C200")  
if error and cl.offset(0, 1).value = yes then    
cl.value = cl.offste(0, -1) Else
Cl.value = cl.value
End if
```
- user2140261 almost 11 years
  
  What are you looking to see is an error you are not testing any variables. You you looking to see if there is an error in cl?
- Doug Glancy almost 11 years
  
  Sean, you need to clarify. You mention a "second column", "a pivot", "the pivots", and error having to do with special cells. In order to get an answer you need to expand and expound upon these, and possibly other, items. And, as always, show us your code.
- Sean Connecticut almost 11 years
  
  Sorry let me be more clear I have a column A for contract names and column C is a pivot that brings up the values of the contract from another table. Where I am getting the error is column B which has an if statement which basically says if column C is "yes" then use contract names. I still want the value if my if statement in column B produces an error
- Sean Connecticut almost 11 years
  
  @DougGlancy as I've previously been down voted for showing too much code / typing too much I try to keep both my codes and questions as simple as possible (omitting parts of my code that aren't relevant to the question) I believe I did a sufficient job as a user below was able to answer my question. Can you please remove your downvote because I don't believe it's fair to be downvoted for doing something and also for not doing it (writting too much/ writing a more condensed answer) if you feel I have done something truly unforgivable I invite you to pm me so I can try to explain myself
- Doug Glancy almost 11 years
  
  My downvote wasn't based on your earlier answers. I simply found it very unclear, which is one of the two listed criteria for downvoting. Note that even though you got an answer it starts with "I am very confused by your question". At any rate, I see that you updated the question with more details, so I've removed the downvote. On a slightly different note, I would heed @user2140261 when they say that there's probably a MUCH better solution. Good luck with your coding!
- Sean Connecticut almost 11 years
  
  @user2140261 sorry to bother you but I was wondering if you could remove your down vote as I edited my question to make it more clear... I'm new to the site so I didn't know the protocol and tried to keep it brief to make it less of a hassle to read
- user2140261 almost 11 years
  
  @SeanConnecticut Sorry, but the none of the down votes were from me.