CODEWITHSUNDEEP
arraysexcelvba
user2599261
user2599261

Reputation: 1

Macro to read cells in one sheet and then search for match in another sheet and add date to column

I'm trying to write an excel macro that will go down a column of text and then search for each item in the column in another spreadsheet in the same workbook. When the value is found it should put todays date in a specific column e.g. column H. I have found some code that will do the first part and another piece of code that will do the second part. I just haven't got the knowledge of coding to put the two together and make it work. The two pieces of code are shown below. 

Sub From_sheet_make_array()

  Dim myarray As Variant
  Dim cells As Range
  Dim cl



      myarray = Range("a1:a10").Value

      Set cells = Worksheets("Sheet2").Columns(1).cells

      Set cl = cells.cells(1)
      Do
      If IsError(Application.Match(cl.Value, myarray, False)) Then
      Exit Sub
      Else:
            i = i + 1
      'This shows each item in column in messagebox
      'Need to pass this value to other code somehow
            MsgBox (cl.Value)

     End If

Set cl = cl.Offset(1, 0) Loop While Not IsEmpty(cl.Value) End Sub

Sub Searchlist()

Dim c As Range
With ActiveWorkbook.Sheets("Sheet1")
' This will search for whatever is in ""
' Somehow need to get cl.Value (myarray) in here
    Set c = .Columns("A").Find(What:="text", _
                               LookIn:=xlFormulas, LookAt:=xlPart, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, MatchCase:=True)
             c.Offset(0, 8).Value = Date
End With
Exit Sub

End Sub

If anyone can stitch them together or point me in the right direction of how to go about it would really help me out. I could be way off as I am very new to this. Thanks for any help offered.

Upvotes: 0

Views: 9265

Answers (1)

Derek Cheng
Derek Cheng

Reputation: 525

From my understanding you're in a process of checking if you have the entry for today in another worksheet and if you do, you put a date-stamp in your current worksheet. Try this: (let me know if it works!)

Sub DateStampIfFound()
Dim cell As Range
Dim temp As Range
For Each cell In Sheets("worksheet_containing_search_criteria").UsedRange.Columns("Specify_the_column").Cells
    'so you dont search for blanks and skipping header row
    If cell <> "" and cell.row<>1 Then
        Set temp = Sheets("worksheet_where_you_want_the_find_to_happen").Columns("Specify_the_column").Find(What:=cell.Value, _
                            LookIn:=xlFormulas, LookAt:=xlPart, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, MatchCase:=True)
        'if found
        If Not temp Is Nothing Then
            'if the search_criteria is in the same sheet
            cell.Offset(0, number_of_columns_offset_from_cell) = Date
        End If
    End If
Next

End Sub

Upvotes: 2

Related Questions