Advertisement
Guest User

Untitled

a guest
Sep 21st, 2017
64
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.41 KB | None | 0 0
  1. Option Explicit
  2. Public Sub Example()
  3. Dim Sht As Worksheet
  4. Dim rng As Range
  5.  
  6. Set Sht = ActiveWorkbook.Sheets("Sheet2")
  7.  
  8. For Each rng In Sht.Range("A1", Range("A11").End(xlUp))
  9.  
  10. rng.Offset(0, 1).Value = Split(rng, " ")(0)
  11.  
  12. Next
  13.  
  14. Set Sht = Nothing
  15. Set rng = Nothing
  16. End Sub
  17.  
  18. Sub one()
  19. Dim food As String, type As String
  20.  
  21. Dim rng As Range
  22. Dim cel As Range
  23.  
  24. Set rng = Range("A:A")
  25.  
  26. For Each cel In rng
  27.  
  28. food = cel.Value
  29.  
  30. If InStr(UCase(food), UCase("pizza")) <> 0 Then
  31. type = "Fast food"
  32. Elseif InStr(UCase(food), UCase("burger")) <> 0 Then
  33. type = "Fast food"
  34. Else
  35. type = "Not Fast food"
  36. End If
  37.  
  38. cel.offset (0, 1).Value = type
  39. Next cel
  40. End Sub
  41.  
  42. Sub Find_and_Copy():
  43. Dim keywords() As Variant
  44. keywords = Array("Pizza", "Burger", "Chicken")
  45.  
  46. Dim endRow As Integer
  47. Dim SearchRng As Range
  48. With Sheets("Sheet1")
  49. endRow = .Cells(Rows.Count, "A").End(xlUp).Row
  50. Set SearchRng = .Range("A1:A" & endRow).Cells
  51. End With
  52.  
  53. Dim r As Range
  54. Dim firstAddress As String
  55. Dim i As Integer
  56. For i = 0 To UBound(keywords):
  57. With SearchRng
  58. Set r = .Find(keywords(i), LookIn:=xlValues)
  59. If Not r Is Nothing Then
  60. firstAddress = r.Address
  61. Do
  62. Cells(r.Row, "B").Value = keywords(i)
  63. Set r = .FindNext(r)
  64. Loop While Not r Is Nothing And r.Address <> firstAddress
  65. End If
  66. End With
  67. Next
  68.  
  69. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement