michaeltsmith93
Board Regular
- Joined
- Sep 29, 2016
- Messages
- 83
For the life of my, I can't figure this out. I'm sure it's obvious. I'm getting a subscript error on the line in red below.
Code:
Sub UpdateWatchlistM()
Dim k1 As Worksheet, k2 As Worksheet
Dim FirstBlankRow As Long, r As Long, StartingRow As Long
Dim ESFound As Range
Set k1 = Worksheets("SubjectSummary")
Set k2 = Worksheets("Watchlist")
StartingRow = 6
FirstBlankRow = k2.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
r = StartingRow
ExistingSubject = k1.Range("D" & r).Value
Do While Not ExistingSubject = ""
'look for existing subject in SubjectSummary
Set ESFound = k2.Columns("D:D").Find(what:=ExistingSubject, LookIn:=xlValues, lookat:=xlWhole)
If ESFound Is Nothing Then
'add info to end of SubjectSummary
Call AddSubjectWatch(r, FirstBlankRow)
FirstBlankRow = FirstBlankRow + 1
Else
'overwrite existing line of main file
Call AddSubjectWatch(r, ESFound.Row)
End If
r = r + 1
ExistingSubject = k1.Range("C" & r).Value
Loop
End Sub
Sub AddSubjectWatch(SearchRow As Long, DestRow As Long)
Dim c As Long
For c = 1 To 4
[COLOR=#ff0000] Worksheets("Watchlist").Cells(DestRow, c).Value = Worksheets("Subject Detail").Cells(SearchRow, c).Value[/COLOR]
Next c
Cells(DestRow, 8).Value = Worksheets("Subject detail").Cells(SearchRow, 8).Value
End Sub