Run-Time Error 1004 on Named Range VBA

[hartsie]

I think this should be easy for someone. I am trying to insert a simple Vlookup function using VBA. For the range I've defined, I am receiving a 1004 error. Below is the image and the script:

1 Range("M2:U" & endrow).Select
2 Set formRng = Selection
3
4 Range("formRng").Formula = "=VLOOKUP($H2,'Entity Property Sheet'!$E:$N,COLUMN()-11,0)"
5
End Sub

The error occurs on line 4, and I am not sure why. For the sake of saving lines, I didn't include the defined dimensions

ERROR HERE>>>>>>>> Range("formRng").Formula = "=VLOOKUP($H2,'Entity Property Sheet'!$E:$N,COLUMN()-11,0)"

 

[jasonb75]

For the sake of saving lines, I didn't include the defined dimensions

If you want to save lines then why not simply use

Range("M2:U" & endrow).Formula = "=VLOOKUP($H2,'Entity Property Sheet'!$E:$N,COLUMN()-11,0)"

The only reason I can see for it throwing that error would be an incorrect definition of endrow.

 

[hartsie]

@jasonb75 thank you!

 

[jasonb75]

You're welcome

As a follow up, I just noticed an error in my diagnosis of the problem, I based my reply on the information in your post (mainly the thread title) rather than what was in the code.

For a 'Named Range' (Excel > Formulas > Name Manager) the line that you used, Range("formRng").Formula = is correct, however when you set a range to a variable in vba it is a range object, not a named range. In this instance you only need to use the name formRng.Formula = , vba already knows that its a range from the definition.

 

[hartsie]

@jasonb75 Can you tell from the script below why my endrow was a bad definition?

msSht.Select
Range("mstr_hrd_rng").Find(fndhdr, Lookat:=xlWhole).Select
Range(Selection, Selection.End(xlDown)).Select
endrow = Selection.Rows.Count

 

[hartsie]

@jasonb75 You are great! Thank you!

 

[jasonb75]

Can you tell from the script below why my endrow was a bad definition?

Having not seen how endrow was defined, I had assumed that the error was caused by it having a value of 0. This was before I noticed that I had incorrectly diagnosed the problem as per my previous reply.

For future reference, Select and Selection are rarely necessary, they do nothing other than cause your code to run slower. Also, when looking for the last row of data it is better to search from the bottom up rather than the top down, for example

endrow = msSht.Range("mstr_hrd_rng").Find(fndhdr, Lookat:=xlWhole).EntireColumn.Cells(Rows.Count, 1).End(xlUp).Row

Although this would be better broken down into smaller lines for error elimination.

Dim msSht As Worksheet, mRng As Range, fCell as Range, fCol As Range, endRow As Long
Set msSht = Worksheets("Sheet1") 'change as necessary.
Set mRng = msSht.Range("mstr_hrd_rng")
Set fCell = mRng.Find(fndhdr, Lookat:=xlWhole)
If Not fCell Is Nothing Then
    Set fCol = fCell.EntireColumn
    endRow = fCol.Cells(Rows.Count, 1).End(xlUp).Row
End If

Note that I have only typed the code out quickly as an example, it may contain errors. If the range, mstr_hrd_rng is defined at workbook level then the msSht definition will not be necessary.

 

[hartsie]

@jasonb75 Thank you for providing that. It looks like it is working well enough.

Also, thanks for the tip about finding the last row... I am really new to all of this. I really appreciate your input. How long have you been working with VBA?