VBA Wrong number of arguments or invalid property assignment

Question

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

Where do you get the error? What is the desired behavior of that code? Please take time to read this link before editing your post to improve it : stackoverflow.com/help/how-to-ask — R3uK
– R3uK, Commented Nov 19, 2015 at 8:20
please see stackoverflow.com/questions/2237873/… to understand what is .Height properties — Fabrizio
– Fabrizio, Commented Nov 19, 2015 at 8:28
Probably Range(d, C6498) is not valid in this way. You could use e.g. Range("A1:C3") or Range(Cells(...), Cells(...)). Maybe you wanted Range("C4:C6498")? — gembird
– gembird, Commented Nov 19, 2015 at 8:28

R3uK · Accepted Answer · 2015-11-19 08:34:22Z

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

Collectives™ on Stack Overflow

VBA Wrong number of arguments or invalid property assignment

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related