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