Pull values from For/Next statement?

[jmpatrick]

Hope this makes sense.

I use the code below to search the CalendarNotesTriggerColumn range of the Calendar sheet and look for a duplicate value. If it finds a match it displays a MsgBox with relevant information from another sheet. What I can't figure out is how to add an additional value (in my case a date) from another column on the Calendar sheet (Column B, which is a date). It should read something similar to: "8254-55 (Lakeview Estates - Lot 55) already exists on 1/15/25. Do you want to create a duplicate?"

The 1/15/25 is what I need to pull from the row that has the duplicate.

For Each rngCELL In Worksheets("Calendar").Range("CalendarNotesTriggerColumn").Cells
  If rngCELL.Value = Worksheets("JobGrid").Range("J1").Value Then
  Ans = MsgBox(Worksheets("JobGrid").Range("J1").Value & " (" & Worksheets("JobGrid").Range("C1").Value & " - Lot " & Worksheets("JobGrid").Range("E1").Value & ") already exists.  Do you want to create a duplicate?", vbYesNo + vbQuestion)
  If Ans = vbNo Then
  End If
Next

 

[Jeffrey Mahoney]

I don't see where you are comparing dates. When the message box pops up it asks the user about a duplicate on a certain date.

Anyway, give this a try

Sub IDontKnow()

  Dim Ans As Variant
  Dim rngCell
  Dim JobGridVal As String
  Dim RowDate As Date
  Dim cSht As Worksheet
  Dim jgSht As Worksheet

  Set cSht = Worksheets("Calendar")
  Set jgSht = Worksheets("JobGrid")
  JobGridVal = jgSht.Range("J1").Value
  
  For Each rngCell In cSht.Range("CalendarNotesTriggerColumn").Cells
    If rngCell.Value = JobGridVal Then
      RowDate = Intersect(rngCell.EntireRow, cSht.Range("B:B")).Value
      Ans = MsgBox(jgSht.Range("J1").Value & " (" & jgSht.Range("C1").Value & " - Lot " & jgSht.Range("E1").Value & ") already exists on " & Format(RowDate, "mm/dd/yy") & ".  Do you want to create a duplicate?", vbYesNo + vbQuestion)
      If Ans = vbNo Then
        'What?
      End If
    End If
  Next

End Sub

 

[jmpatrick]

I don't see where you are comparing dates. When the message box pops up it asks the user about a duplicate on a certain date.

Anyway, give this a try

Sub IDontKnow()

  Dim Ans As Variant
  Dim rngCell
  Dim JobGridVal As String
  Dim RowDate As Date
  Dim cSht As Worksheet
  Dim jgSht As Worksheet

  Set cSht = Worksheets("Calendar")
  Set jgSht = Worksheets("JobGrid")
  JobGridVal = jgSht.Range("J1").Value
 
  For Each rngCell In cSht.Range("CalendarNotesTriggerColumn").Cells
    If rngCell.Value = JobGridVal Then
      RowDate = Intersect(rngCell.EntireRow, cSht.Range("B:B")).Value
      Ans = MsgBox(jgSht.Range("J1").Value & " (" & jgSht.Range("C1").Value & " - Lot " & jgSht.Range("E1").Value & ") already exists on " & Format(RowDate, "mm/dd/yy") & ".  Do you want to create a duplicate?", vbYesNo + vbQuestion)
      If Ans = vbNo Then
        'What?
      End If
    End If
  Next

End Sub

Sorry I wasn’t clear. It looks for duplicate job numbers. If it finds one it reports back. If it finds a duplicate I need it to pull the date where it is. I’ll try your code tomorrow. Thanks!

 

[jmpatrick]

I don't see where you are comparing dates. When the message box pops up it asks the user about a duplicate on a certain date.

Anyway, give this a try

Sub IDontKnow()

  Dim Ans As Variant
  Dim rngCell
  Dim JobGridVal As String
  Dim RowDate As Date
  Dim cSht As Worksheet
  Dim jgSht As Worksheet

  Set cSht = Worksheets("Calendar")
  Set jgSht = Worksheets("JobGrid")
  JobGridVal = jgSht.Range("J1").Value
 
  For Each rngCell In cSht.Range("CalendarNotesTriggerColumn").Cells
    If rngCell.Value = JobGridVal Then
      RowDate = Intersect(rngCell.EntireRow, cSht.Range("B:B")).Value
      Ans = MsgBox(jgSht.Range("J1").Value & " (" & jgSht.Range("C1").Value & " - Lot " & jgSht.Range("E1").Value & ") already exists on " & Format(RowDate, "mm/dd/yy") & ".  Do you want to create a duplicate?", vbYesNo + vbQuestion)
      If Ans = vbNo Then
        'What?
      End If
    End If
  Next

End Sub

This works fine. Thanks!