CODEWITHSUNDEEP
vbaexcel
kiminem
kiminem

Reputation: 39

VBA Wrong number of arguments or invalid property assignment

Out of touch with vba and so i am sure its a silly mistake somewhere. Would be really helpful if someone could point that out

Code:

Private Function generate() As Integer

Dim source_size As Long
Dim target_size As Long
Dim i As Long
Dim j As Long
Dim count As Long
Dim source1 As Range
Dim target1 As Range

Set source1 = Worksheets("Filter").Range(C4, C6498)
Set target1 = Worksheets("30").Range(A2, AP95787)


source_size = source1.Height
target_size = target1.Height

For i = 1 To source_size Step 1
    For j = 1 To target_size Step 1
        If Application.source1.Cells(i, 1).Value = target1.Cells(j, 5).Value Then
            target1.Row(j).Select
            'Selection.Copy
            Worksheet("result").Range("A1").Rows("1:1").Insert Shift:=xlDown
        End If
    Next j
Next i
generate = 0


End Function

Upvotes: 0

Views: 50917

Answers (1)

R3uK
R3uK

Reputation: 14537

First you had an issue of declaring your ranges, C4 as itself in VBA is considered as a variable, you need to use one these :

[C4] or Range("C4") or Cells(4,3) or Cells(4,"C")

So your lines or defining ranges should look like this :

Set source1 = Worksheets("Filter").Range([C4], [C6498])
Set target1 = Worksheets("30").Range(Range("A2"), Range("AP95787"))

Secondly, the .Height property will give you the size of the range, not the number of rows, to get the number of rows, you need to use Range(...).Rows.Count

source_size = source1.Rows.count
target_size = target1.Rows.count

Here is your full code :

Option Explicit
Public Function generate() As Integer
Dim source_size As Long
Dim target_size As Long
Dim i As Long
Dim j As Long
Dim count As Long
Dim source1 As Range
Dim target1 As Range

Set source1 = Worksheets("Filter").Range("C4:C6498")
Set target1 = Worksheets("30").Range("A2:AP95787")
source_size = source1.Rows.count
target_size = target1.Rows.count

For i = 1 To source_size Step 1
    For j = 1 To target_size Step 1
        If Application.source1.Cells(i, 1).Value = target1.Cells(j, 5).Value Then
            target1.Rows(j).Select
            'Selection.Copy
            Worksheets("result").Range("A1").Rows("1:1").Insert Shift:=xlDown
        End If
    Next j
Next i
generate = 0
End Function

Upvotes: 3

Related Questions