-3

Hello im fairly new to vba but i been struggling with a code in working on, i need to check if the dates on sheet1 match another in sheet2 and copy data from sheet2 to sheet1 if the corresponding row from sheet2 contains a certain text string. Any help would be appreciated.

Community
  • 1
  • 1
Roger
  • 11
  • 2

2 Answers2

2

I created for you an Excel file wich contains three sheets (Sheet1, Sheet2 and Sheet3). On the first sheet, I have the data as below:

ValueDate   Operation   User
12/08/2016  Operation1  SYS
13/08/2016  Operation2  MAN
14/08/2016  Operation3  SYS
15/08/2016  Operation4  MAN
16/08/2016  Operation5  SYS
17/08/2016  Operation6  MAN
18/08/2016  Operation7  SYS
19/08/2016  Operation8  MAN
20/08/2016  Operation9  SYS
21/08/2016  Operation10 MAN
22/08/2016  Operation11 SYS
23/08/2016  Operation12 MAN
24/08/2016  Operation13 SYS
25/08/2016  Operation14 MAN

On the second I have the details of the operations by line:

Date    Code1   Code2   Code3   Code4   Obs.
12/08/2016  ABR                        Abreviation
12/08/2016       SPL                   Spelling
12/08/2016                      OTH    Others
15/08/2016              CHK            Checklist
16/08/2016  ABR                        Abreviation
17/08/2016  ABR                        Abreviation
17/08/2016       SPL                   Spelling
19/08/2016  ABR                        Abreviation

For each date of the sheet1, it is sought whether there are matches in sheet2. If so, we copy the found row of sheet2 into a new row of sheet3. This is the VBA code to do this.

Sub findMatching()
Dim CurrentLine As Long, CurrentLine2 As Long, CurrentLine3 As Long
Dim MaxRows As Long, MaxRows2 As Long

MaxRows = 20
MaxRows2 = 25
CurrentLine3 = 2 '-- We start at second line because header in the first   line
'Fill Heading sheet3
Sheets(3).Cells(1, 1) = Sheets(2).Cells(1, 1)
Sheets(3).Cells(1, 2) = Sheets(2).Cells(1, 2)
Sheets(3).Cells(1, 3) = Sheets(2).Cells(1, 3)
Sheets(3).Cells(1, 4) = Sheets(2).Cells(1, 4)
Sheets(3).Cells(1, 5) = Sheets(2).Cells(1, 5)
For CurrentLine = 1 To MaxRows '-- Loop in first sheet (read data)
    For CurrentLine2 = 1 To MaxRows2 '-- Loop in second sheet (compare data)
      If Sheets(1).Cells(CurrentLine, 1) = Sheets(2).Cells(CurrentLine2, 1) Then
      '-- copying matching data
    Sheets(3).Cells(CurrentLine3, 1) = Sheets(2).Cells(CurrentLine2, 1)
    Sheets(3).Cells(CurrentLine3, 2) = Sheets(2).Cells(CurrentLine2, 2)
    Sheets(3).Cells(CurrentLine3, 3) = Sheets(2).Cells(CurrentLine2, 3)
    Sheets(3).Cells(CurrentLine3, 4) = Sheets(2).Cells(CurrentLine2, 4)
    Sheets(3).Cells(CurrentLine3, 5) = Sheets(2).Cells(CurrentLine2, 5)
    CurrentLine3 = CurrentLine3 + 1
  End If
   Next CurrentLine2
 Next CurrentLine
 '-- If the date in the first column isn't formatted well.
 Sheets(3).Columns("A:A").Select
 Selection.NumberFormat = "m/d/yyyy"
 Sheets(3).Range("A1").Select
End Sub

Below the result

Date    Code1   Code2   Code3   Code4
12/08/2016  ABR         
12/08/2016        SPL       
12/08/2016                      OTH
15/08/2016              CHK 
16/08/2016  ABR         
17/08/2016  ABR         
17/08/2016        SPL       
19/08/2016  ABR

Hope this can help!

Mohamad TAGHLOBI
  • 581
  • 5
  • 11
  • I apologize i posted from a phone earlier thanks mohamed but that seems too complicated for me at this level but this is my code so far it still adds the data even when the dates dont match – Roger Nov 19 '16 at 02:58
1
   <code>
    With SrchRng
Range("H22").Select
Do Until i = 120
If InStr(1, Cells(j, 8), Srchwrd) < 0 And Activecells.Offset(0, -6).Value
Master.Cells(i, 2) Then _
  Master.Cells(i, 3).Value = Cells(j, 8).Offset(0, -2).Value _
 And i = i + 1
   j = j + 1

         If NCB.Cells(j, 2).Value <> Master.Cells(i, 2).Value _
   Then Master.Cells(i, 3).Value = "No Entry Today"
  i = i + 1
 j = j + 1
Roger
  • 11
  • 2