CODEWITHSUNDEEP
vbaexcel
Damjan
Damjan

Reputation: 47

VBA - How to loop

I'm pretty new into this and I got stuck.

If I have a text string in column A (A1:A10) let's say. And I have a macro that looks for a keyword in that string, if it's found I want a word to be entered into column B (B1:B10).

For example A1-Pizza Hut - B1 Pizza, A2 Burger King - B2 Burger.

I got to the point where I can find the keyword, but when I try to do anything that would loop through the range, I always end up getting the same result in B.

Upvotes: 1

Views: 382

Answers (3)

0m3r
0m3r

Reputation: 12495

Use a For Each Loop & Split:

Option Explicit
Public Sub Example()
    Dim Sht As Worksheet
    Dim rng As Range

    Set Sht = ActiveWorkbook.Sheets("Sheet2")

    For Each rng In Sht.Range("A1", Range("A11").End(xlUp))

        rng.Offset(0, 1).Value = Split(rng, " ")(0)

    Next

    Set Sht = Nothing
    Set rng = Nothing
End Sub

Upvotes: 0

Damjan
Damjan

Reputation: 47

Thank you for the answers. I thought I posted my code, but I guess it didn't. Anyways I figured out a way after looking online for the whole day.

Sub one()
Dim food As String, type As String

Dim rng As Range
Dim cel As Range

Set rng = Range("A:A")

For Each cel In rng

   food = cel.Value

   If InStr(UCase(food), UCase("pizza")) <> 0 Then
      type = "Fast food"
   Elseif InStr(UCase(food), UCase("burger")) <> 0 Then
      type = "Fast food"
   Else
      type = "Not Fast food"
  End If 

  cel.offset (0, 1).Value = type
Next cel
End Sub

Upvotes: 1

Amorpheuses
Amorpheuses

Reputation: 1423

This should do what you want:

Sub Find_and_Copy():
  Dim keywords() As Variant
  keywords = Array("Pizza", "Burger", "Chicken")

  Dim endRow As Integer
  Dim SearchRng As Range
  With Sheets("Sheet1")
    endRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRng = .Range("A1:A" & endRow).Cells
  End With

  Dim r As Range
  Dim firstAddress As String
  Dim i As Integer
  For i = 0 To UBound(keywords):
    With SearchRng
      Set r = .Find(keywords(i), LookIn:=xlValues)
      If Not r Is Nothing Then
          firstAddress = r.Address
          Do
            Cells(r.Row, "B").Value = keywords(i)
            Set r = .FindNext(r)
          Loop While Not r Is Nothing And r.Address <> firstAddress
      End If
    End With
  Next

End Sub

It will find all occurrences of each entry in the 'keywords' array that matches cells of column "A" - and of course, set column "B" to that keyword.

Note that say you have an entry like "ala Burger Chicken" it'll put 'Chicken' (which I added to 'keywords' just to keep in the spirit of things) in column B for that row because that's the last thing it did a search for - hence overwriting the previous 'Burger' entry that was in that cell.

Upvotes: 0

Related Questions