Table Update VBA - byref argument type mismatch

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.

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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Since w2 is an object variable that has been assigned a Workbook object, and since it looks like you want to pass the assigned workbook to AddSubject, the call should be...

Code:
Call AddSubject(w2, r, FirstBlankRow)

Also, SearchFilename in AddSubject should be declared as a Workbook object, not Workbooks...

Code:
Sub AddSubject(SearchFilename As Workbook, SearchRow As Long, DestRow As Long)

Also, when you declare w1 and w2 this way, as you done in your code...

Code:
Dim w1, w2 As Workbook

...w1 gets typed as a Variant, not a Workbook object. So you should declare them like this...

Code:
Dim w1 as Workbook, w2 As Workbook

Hope this helps!
 
Last edited:
Upvote 0
You're very welcome, and thanks for the feedback!

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top