michaeltsmith93
Board Regular
- Joined
- Sep 29, 2016
- Messages
- 83
Hi there,
I'm trying to update the information in a subset of columns in my table from another workbook. A bit of background on the data format and my objective.
Workbook 2 (source)
Columns A:K (1 to 11), each row is a person
Workbook 1 (destination)
The data from Workbook 2 are offset by one column (B:L, 2 to 12)
Information for people in Workbook 1 is updated, and people are added. So people are meant to be added by what's in red, and entries are updated via what's in blue.
I am getting a byref argument type mismatch for r or FirstBlankRow. It appears to be a Long in all instances. What am I missing? I'm sure I'm doing something stupid. Also, please feel free to take a look for any other issues.
I'm trying to update the information in a subset of columns in my table from another workbook. A bit of background on the data format and my objective.
Workbook 2 (source)
Columns A:K (1 to 11), each row is a person
Workbook 1 (destination)
The data from Workbook 2 are offset by one column (B:L, 2 to 12)
Information for people in Workbook 1 is updated, and people are added. So people are meant to be added by what's in red, and entries are updated via what's in blue.
I am getting a byref argument type mismatch for r or FirstBlankRow. It appears to be a Long in all instances. What am I missing? I'm sure I'm doing something stupid. Also, please feel free to take a look for any other issues.
Code:
Private Sub OK_Click()
Dim SDReport As String
Dim w1, w2 As Workbook
Dim k1, k2 As Worksheet
Dim FirstBlankRow, r, StartingRow As Long
Dim ESFound As Range
Set w1 = Workbooks("BAN2401-301_ScreeningSummary.xlsm")
Set w2 = Workbooks(Me.FileName & ".xlsx")
Set k1 = w1.Worksheets("CognitiveDetails")
Set k2 = w2.Worksheets("Subject Detail")
SDReport = k2
StartingRow = 2
FirstBlankRow = 53
r = StartingRow
ExistingSubject = Workbooks(SDReport).ActiveSheet.Range("C" & r).Value
Do While Not ExistingSubject = ""
'look for existing subject in CognitiveDetails
Set ESFound = Columns("D:D").Find(what:=ExistingSubject, LookIn:=xlValues, lookat:=xlWhole)
If ESFound Is Nothing Then
'add info to end of CognitiveDetails
[COLOR=#ff0000] Call AddSubject(Workbooks(w2), r, FirstBlankRow)[/COLOR]
[COLOR=#ff0000] FirstBlankRow = FirstBlankRow + 1[/COLOR]
Else
[COLOR=#0000ff] 'overwrite existing line of main file[/COLOR]
[COLOR=#0000ff] Call AddSubject(Workbooks(w2), r, ESFound.Row)[/COLOR]
End If
r = r + 1
ExistingSubject = Workbooks(SDReport).Worksheets("Subject Detail").Range("C" & r).Value
Loop
End Sub
Sub AddSubject(SearchFilename As Workbooks, SearchRow As Long, DestRow As Long)
For c = 1 To 11
Cells(DestRow, c + 1).Value = Workbooks(SearchFilename).Worksheets("Subject Detail").Cells(SearchRow, c).Value
Next c
End Sub