VBA, If value Like

user_8462962 0
Viewed 7875

I have a column(x) and trying to do a If and like in another column(d). Getting a range error on the selection autofill part.

Dim src As Range
Set src = Worksheets("File").Range("2:17783")


    If (Range("x2").Value Like "*ProductType:'FXD';*") Then
                         Range("D2").Value = "FXD"
            ElseIf (Range("x2").Value Like "*;ProductSubType:'SWLEG'*") Then
                         Range("D2").Value = "XSW" 
            End If 

Selection.AutoFill Destination:=src.Columns("D")
vba excel
3 Answers

Instead of doing autofill just set the value at once.

Also do not forget to provide the sheet parent to all your range objects:

Dim src As Range
Set src = Worksheets("File").Range("2:17783")


If (Worksheets("File").Range("x2").Value Like "*ProductType:'FXD';*") Then
    Worksheets("File").Range("D2:D17783").Value = "FXD"
ElseIf (Worksheets("File").Range("x2").Value Like "*;ProductSubType:'SWLEG'*") Then
    Worksheets("File").Range("D2:D17783").Value = "XSW" 
End If
user_48515900

Try using column X to set the extents of the values to fill in column D.

with worksheets("file")
    select case true
        case .cells(2, "X").value2 like "*ProductType:'FXD';*"
            .range(.cells(2, "D"), .cells(.cells(.rows.count, "X").end(xlup).row, "D")) = "FXD"
        case .cells(2, "X").value2 like "*;ProductSubType:'SWLEG'*"
            .range(.cells(2, "D"), .cells(.cells(.rows.count, "X").end(xlup).row, "D")) = "XSW"
        case else
            'do nothing
    end select
end with
Admin1

Try changing Selection.AutoFill Destination:=src.Columns("D") to Selection.AutoFill Destination:=src.Columns("D:D")

This is going to drag that formula all the way down column D, which is what I assumed you're trying to do, however, it will literally go down to row 1048576, so unless you're trying to do that, change the second range to D & whatevervaluehere

user_76686130
Related