VBA 'Range' of object'_Global' fail in Do Until-CodePudding

Basically i just found this little code on the web, i thought it might help me because i want to improve it. But on the Do Until Range("A" & amp, R) = "" line i got the mentioned error in the title. Here is the code:

R = 1
'loop to the last row
Do Until Range("A" & amp, R) = ""
'check each cell if contains 'apple' then..
'..place 'Contains Apple' on column B
If Range("A" & amp, R) Like "*apple*" Then
Range("B" & amp, R) = "Contains Apple"
End If
R = R   1
Loop
End Sub

It does search the "apple" term in the sentences in A column and write "contains apple" in the B column if it contains "apple"

CodePudding user response：

Try this instead:

R = 1
'loop to the last row
Do Until Range("A" & R).Value = ""
'check each cell if contains 'apple' then..
'..place 'Contains Apple' on column B
If Range("A" & R).Value Like "*apple*" Then
    Range("B" & R).Value = "Contains Apple"
End If
R = R   1
Loop

CodePudding user response：

Range("A" & amp, R) is not the right way to address the range. When you copied from the website, it copied the html encoding as well. In Html & is encoded as &. Simply replace & amp, with & in your code. So your code becomes

R = 1
'loop to the last row
Do Until Range("A" & R) = ""
    'check each cell if contains 'apple' then..
    '..place 'Contains Apple' on column B
    If Range("A" & R) Like "*apple*" Then
        Range("B" & R) = "Contains Apple"
    End If
    R = R   1
Loop

Also to make the code case insensitive, as suggested by @VBasic2008 in comments below, you may want to change Range("A" & R) Like "*apple*" to If LCase(Range("A" & R).Value2) Like "*apple*" Then.

Having said that, I would use a different approach than using a loop which is a slightly slower approach.

It does search the "apple" term in the sentences in A column and write "contains apple" in the B column if it contains "apple"

If you were to do this in Excel, then you would use the formula =IF(ISNUMBER(SEARCH("apple",A1)),"Contains Apple","")

So what we will do is find the last row in Column B and then add this formula in the entire range in one go! Finally we will convert the formula to values.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim sFormula As String
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    '~~> This is your formula
    '   =IF(ISNUMBER(SEARCH("apple",A1)),"Contains Apple","")
    sFormula = "=IF(ISNUMBER(SEARCH(""apple"",A1)),""Contains Apple"","""")"
    
    With ws
        '~~> Find the last row in column B
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Insert the formula in the entire range in 1 go
        With .Range("B1:B" & lRow)
            .Formula = sFormula
            '~~> Convert formula to value
            .Value = .Value
        End With
    End With
End Sub