Advertisement
Guest User

Search string in array first column

a guest
Feb 19th, 2020
136
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Option Explicit
  2.  
  3. Sub Arrays()
  4.  
  5. 'Dimensions
  6. Dim arComments As Variant
  7. Dim sht As Worksheet
  8. Dim LastRow As Long
  9. Dim LastColumn As Long
  10. Dim ArrayRange As String
  11. Dim i As Long
  12. Dim shtData As Worksheet
  13. Dim stringToFind As String
  14.  
  15. 'Set variables
  16. Set sht = ThisWorkbook.Worksheets("Comments")
  17. Set shtData = ThisWorkbook.Worksheets("Data")
  18.  
  19. 'Get last row for comments and data sheet
  20. LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
  21. LastRowData = shtData.Cells(shtData.Rows.Count, "A").End(xlUp).Row
  22.  
  23. 'Set the size of the array
  24. ArrayRange = "A2" & ":J" & LastRow
  25.  
  26. 'Define array of comments
  27. arComments = Sheets("Comments").Range(ArrayRange).Value
  28.  
  29. 'Start looping through the IDs from row 4
  30. For i = 4 To LastRowData
  31.     'Define string to find
  32.    stringToFind = Sheets("Data").Range("A" & i).Value
  33.     'Is stringToFind found in the first column of arComments array
  34.    '#################### How do I do this? #########################
  35.    'if found, populate column B to J in Data sheet with arComments array values
  36.        Debug.Print "Its working"
  37.     End If
  38. 'if not found, go to next row on data sheet and repeat checks
  39. Next i
  40.  
  41.  
  42. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement