CODEWITHSUNDEEP
vbaexcel
mrwave
mrwave

Reputation: 97

Error 1004 methold "range" of 'worksheet'_ failed

Hi all I can't seem to solve this error.

The error is on line : lastrmale = malesheet.Range("A" & malesheet.Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Row

The codes above searches sheet "male" and "female" for matches on column A, K and M. If all 3 columns matches, the row from both sheets is then copied and pasted on sheet "Mix" .

Does anyone know the solution to this? Any help is appreciated !

Dim lastr As Long
Dim lastrmale As Long
Dim lastrfemale As Long
Dim lastrmix As Long
Dim malesheet As Worksheet
Dim Femalesheet As Worksheet
Dim mixsheet As Worksheet
Dim i As Long
Set malesheet = Worksheets("Male")
Set Femalesheet = Worksheets("Female")
Set mixsheet = Worksheets("mix")
lastrmale = malesheet.Range("A" & malesheet.Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Row

lastrfemale = Femalesheet.Range("A" & Femalesheet.Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Row

lastr = WorksheetFunction.Min(lastrmale, lastrfemale)
lastrmix = 2
For i = 2 To lastr

    If (malesheet.Range("A" & i).value = Femalesheet.Range("A" & i).value) And (malesheet.Range("K" & i).value = Femalesheet.Range("K" & i).value) And (malesheet.Range("M" & i).value = Femalesheet.Range("M" & i).value) Then

        malesheet.Rows(i & ":" & i).Copy
        mixsheet.Range("A" & lastrmix).PasteSpecial xlPasteAll
    lastrmix = lastrmix + 1
    Femalesheet.Rows(i & ":" & i).Copy
        mixsheet.Range("A" & lastrmix).PasteSpecial xlPasteAll
    lastrmix = lastrmix + 1

    End If
Next

Upvotes: 0

Views: 117

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

The line that's blowing up is an assignment. The right side of that assignment is a value expression; value expressions can be pretty much as long as you can make them, VBA won't feel a thing. But the longer they are, the harder they are to read, maintain, and fix when they break:

lastrmale = malesheet.Range("A" & malesheet.Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Row

You have no less than 6 member calls in that expression:

  • malesheet.Range("A" & ...)
  • malesheet.Range("A1")
  • [Range].SpecialCells(xlLastCell)
  • [Range].Row
  • [Range].End(xlUp)
  • [Range].Row

Make yourself a function that takes a Range or a Worksheet and returns a Long representing the last row.

Now you have:

lastrmale = malesheet.Range("A" & FindLastRow(malesheet.Range("A1")) + 1).Row

Which could also be:

Dim rename_me As Long
rename_me = FindLastRow(malesheet.Range("A1")
lastrmale = malesheet.Range("A" & rename_me + 1).Row

Which seems silly, since the row number you're after would simply be rename_me + 1.

Upvotes: 1

Eric K.
Eric K.

Reputation: 834

I believe that below code you try to find out the last row. But I think it will not work

lastrmale = malesheet.Range("A" & malesheet.Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Row

it suppose is

lastrmale  = malesheet.cells.SpecialCells(xlLastCell).Row + 1

by the way either cells or Range("A1") both are same

malesheet.cells.SpecialCells(xlLastCell).Row + 1  = malesheet.Range("A1").SpecialCells(xlLastCell).Row + 1

or the method I love the most

lastrmale = malesheet.Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Upvotes: 0

Shep
Shep

Reputation: 628

As the error states, your range definition is wrong.

Look at this line as a correct example:

If (malesheet.Range("A" & i).value = ...

Notice the range will resolve as A1, A2,.. etc

For the line throwing the error, what cells is the range referring to ?

malesheet.Range("A" & malesheet.Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Row

Try break up the parts of that operation to confirm the values are as expected. Set break-points or write the values to other cells for debugging: e.g.

Dim temp = xlLastCell     '(does this value exist)
Dim temp2 = xlUp    '(does this value exist)
Dim temp3 = malesheet.Range("A1").SpecialCells(xlLastCell).Row '(does this return an int)
Dim temp4 = malesheet.Range("A1").End(xlUp).Row   '(does this work)
Dim temp5 = malesheet.Range("A" & 1 + 1)   '(does this work)

Once you have confirmed the underlying values, you can hard code the values for a test of the range.

Upvotes: 1

Related Questions