CODEWITHSUNDEEP
excelvbaloopsif-statement
Sean Connecticut
Sean Connecticut

Reputation: 305

iferror statement for macros in vba excel

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

Upvotes: 0

Views: 48820

Answers (1)

user2140261
user2140261

Reputation: 8003

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.

Upvotes: 1

Related Questions