424 VBA Error - If Statement

[taostep1]

Hi guys,

I am trying to run VBA macro to search the words "Red" or "Blue" in columns I, and if the condition is true it would need to process an RC[-9] formula in column N, accordingly.

However, I keep getting a 424 error per the red font below.

Any help would be great.

Sub test()


Sheets("Intern Confirmed Movement").Select

Dim Lastrow As Integer
Dim columnValues As Range, columnLookup As Range, i As Long
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set columnValues = Range("N4:N" & Lastrow).Select
Set columnValues = Selection
Set columnLookup = Range("I4:I" & Lastrow).Select
Set columnLookup = Selection

For i = 1 To columnValues.Rows.Count

If columnLookup.Cells(i, 1).Value = "Red" Or columnLookup.Cells(i, 1).Value = "Blue" Then

columnValues.Cells(i, 1).Value = "=RC[-9]"


End If
Next


End Sub

 

[jproffer]

Try taking the ".Select" off. It's going to error out on the Set columnLookup line too. Remove the 2nd and 4th lines all-together. If it don't work with ".Select" removed, try adding ".Address"

EDIT: That sounds kind of confusing the way I typed it. It would be either:

[COLOR=#ff0000]Set columnValues = Range("N4:N" & Lastrow)[/COLOR]
    Set columnLookup = Range("I4:I" & Lastrow)

or

[COLOR=#ff0000]Set columnValues = Range("N4:N" & Lastrow).Address[/COLOR]
    Set columnLookup = Range("I4:I" & Lastrow).Address

 

[Trebor76]

You could just put the relevant formula down column N:

Option Explicit
Sub Macro3()

    Dim lngLastRow As Long
    Dim wsSource   As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsSource = Sheets("Intern Confirmed Movement")
    
    lngLastRow = wsSource.Cells(Rows.Count, "I").End(xlUp).Row 'Set the variable based on the last Row from Col. I
    
    With wsSource.Range("N4:N" & lngLastRow)
        .Formula = "=IF(OR(I4=""Blue"",I4=""Red""),E4,"""")"
        .Value = .Value 'Convert the above formulas to values. Comment out if you want them to remain.
    End With
    
    Set wsSource = Nothing
    
    Application.ScreenUpdating = True

End Sub

Just a thought.

Robert

 

[taostep1]

Thank you Trebor! That worked like a charm.

But what happens if I wanted the IF formula to be any words that are not "Blue" or "Red", instead?

You could just put the relevant formula down column N:

Option Explicit
Sub Macro3()

    Dim lngLastRow As Long
    Dim wsSource   As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsSource = Sheets("Intern Confirmed Movement")
    
    lngLastRow = wsSource.Cells(Rows.Count, "I").End(xlUp).Row 'Set the variable based on the last Row from Col. I
    
    With wsSource.Range("N4:N" & lngLastRow)
        .Formula = "=IF(OR(I4=""Blue"",I4=""Red""),E4,"""")"
        .Value = .Value 'Convert the above formulas to values. Comment out if you want them to remain.
    End With
    
    Set wsSource = Nothing
    
    Application.ScreenUpdating = True

End Sub

Just a thought.

Robert

 

[Trebor76]

To return what's in Col. E if there's anything in Col. I change this line of my existing code...

.Formula = "=IF(OR(I4=""Blue"",I4=""Red""),E4,"""")"

...to this:

.Formula = "=IF(LEN(I4)>0,E4,"""")"

HTH

Robert