Comparing Time from a range and get value depending on that

[justinxav1]

Hey guys,

Im running a code where i start with one value(The name of analyst) from a column from one sheet1(ACW Data) and loop it through another column in another sheet2(SSG) to search if it matches the name then check corresponding start time from sheet1(Acw Data) is between the 1st spec, 2nd spec and 3rd spec, if Yes then the value in column 'Spec/Non Spec' in Sheet1 (Acw Data) becomes Spec .

For now i have only tried the code to check column within 1st spec. Here it gives me all values as Non Spec. Kindly Assist

Private Sub CommandButton2_Click()

Dim LastRow As Long, intLastrow As Long
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim i As Integer, j As Integer, name As String, Tim1 As String
Dim Value1 As String, Value2 As String


Application.ScreenUpdating = False

Set shtDest = Workbooks("Main file.xlsm").Sheets("acw data")
Set shtSrc = Workbooks("Main file.xlsm").Sheets("SSG")


LastRow = shtSrc.Cells(shtSrc.Rows.Count, "M").End(xlUp).Row
intLastrow = shtDest.Cells(shtDest.Rows.Count, "D").End(xlUp).Row

For j = 2 To intLastrow Step 1
                     
        Value = shtDest.Cells(j, 2).Value
        'Set Found = shtSrc.Columns("B").find(What:=Value, LookIn:=xlValues, Lookat:=xlWhole)
       
        For i = 2 To LastRow Step 1
       
        name = shtSrc.Cells(i, 2).Value
        If (Value = name) Then
       
        Tim1 = Format(shtDest.Cells(j, 4).Value, General)
        Value1 = Format(shtSrc.Cells(i, 13).Value, General)
        Value2 = Format(shtSrc.Cells(i, 14).Value, General)
       
        If (Tim1 >= Value1) Then
            shtDest.Cells(j, 11).Value = "Spec"
           
            Else
           
            shtDest.Cells(j, 11).Value = "NonSpec"
            End If
           End If
            Next i
            Next j
           
           

End Sub

 

[Nebu]

Hi:

There is nothing wrong with your macro the issue is with your time field, I have fixed it find the attached.

Thanks

 

[justinxav1]

Hi Nebu,

Wow, Thanku thanku thanku thanku, thanks a ton.
Im still a beginner with excel and vba and i appreciate all the help i can get, once again thank you very much.

Regards,
Justin X.