VBA - How to loop

Question

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.

Share your code so far. Edit your question and copy it in. Explain in context. — teylyn
– teylyn, Commented Feb 13, 2017 at 22:03
Include the code of your keyword macro in order to ask for help on how to loop it. — VBA Pete
– VBA Pete, Commented Feb 13, 2017 at 22:24

0m3r · Accepted Answer · 2017-02-15 03:10:15Z

1

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

edited Feb 15, 2017 at 3:10

0m3r

12.5k15 gold badges40 silver badges77 bronze badges

answered Feb 14, 2017 at 21:12

Damjan

471 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Graham · Accepted Answer · 2017-09-21 21:21:11Z

0

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

edited Sep 21, 2017 at 21:21

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Feb 13, 2017 at 22:56

0m3r

12.5k15 gold badges40 silver badges77 bronze badges

1 Comment

Damjan Over a year ago

I think that's the closest to the solution that I got. I'm still a newbie in using vba, so all these codes seem good to me, but I really have to get more familiar wth the language so I can understand what each code gives me. Thanks for sharing your answer.

Amorpheuses · Accepted Answer · 2017-02-14 06:43:42Z

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.

Collectives™ on Stack Overflow

VBA - How to loop

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related