VBA Paste Error (because "Nothing" is copied)

[gerald24]

Sub FT()

    Dim InsType As Range
    Dim Rng As Range
    

    Sheets("Computation").Select
    
   For Each InsType In Range("H2", Range("H" & Rows.Count).End(xlUp))
            If InsType.Value = "Cross Rate" Or InsType.Value = "Currency" And InStr(InsType.Offset(0, 8).Value, "Yes") Then
                If Rng Is Nothing Then
                    Set Rng = InsType.Offset(0, -6)
                Else
                    Set Rng = Union(Rng, InsType.Offset(0, -6))
                End If
            End If
    Next InsType
            If Not Rng Is Nothing Then Rng.Copy
            Sheets("Load to VA").Select
            Range("A3").Select
            ActiveCell.PasteSpecial xlPasteValues
            Selection.NumberFormat = "General"
                        
End Sub

I think the error is because Rng is = nothing, I don't know why but what I want to happen is if Rng is nothing, it should not do anything.

advance Thanks!!!!

 

[Trebor76]

Hi gerald24,

The issue is around the way you've constructed the If statement. This should do the job:

Option Explicit
Sub FT()

    Dim InsType As Range
    Dim Rng As Range

    Sheets("Computation").Select
    
   For Each InsType In Range("H2", Range("H" & Rows.Count).End(xlUp))
            If InsType.Value = "Cross Rate" Or InsType.Value = "Currency" And InStr(InsType.Offset(0, 8).Value, "Yes") Then
                If Rng Is Nothing Then
                    Set Rng = InsType.Offset(0, -6)
                Else
                    Set Rng = Union(Rng, InsType.Offset(0, -6))
                End If
            End If
    Next InsType
    If Not Rng Is Nothing Then
        Rng.Copy
        Sheets("Load to VA").Select
        Range("A3").Select
        ActiveCell.PasteSpecial xlPasteValues
        Selection.NumberFormat = "General"
    End If
                        
End Sub

Regards,

Robert

 

[gerald24]

Hi Robert,

I think the "IF NOT" Statement does not need "End If". An error says "Compile Error: End If without block if"

The actual error I am getting (without the End If) is "Run-time error '1004': PasteSpecial Method of Range class failed" which I think is due to the code is doing the paste function even thought there's nothing copied.

 

[Trebor76]

I think the "IF NOT" Statement does not need "End If".

The way you've written it doesn't but if step through your code (by pressing F8) you'll see what I mean. Your code will go straight to the Sheets("Load to VA").Select line if the Rng variable is nothing (i.e. hasn't been set) which is incorrect.

An error says "Compile Error: End If without block if"

That's odd. I don't get that error

The actual error I am getting (without the End If) is "Run-time error '1004': PasteSpecial Method of Range class failed" which I think is due to the code is doing the paste function even thought there's nothing copied.

Correct - see my first point.

 

[gerald24]

Hi Robert,

Your code worked perfectly. Could you education me on what you have tweaked in my codes? Also what is "Option Explicit" fore?

Thank you!

 

[Trebor76]

Your code worked perfectly.

You're welcome. Thanks for like

Could you education me on what you have tweaked in my codes?

All I did was change the way your IF statement was structured to the standard logic test, value if true, value if false (which there was nothing to do).

Option Explicit

This forces you to declare all your variables. If you don't use it any variables that are not declared are assigned as a variant - the most expensive type of variable in programming terms.

HTH

Robert