Run-Time Error 1004 on Named Range VBA

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
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:

1593838283147.png


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)"
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
For the sake of saving lines, I didn't include the defined dimensions
If you want to save lines then why not simply use
VBA Code:
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016

ADVERTISEMENT

@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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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.

VBA Code:
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

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
@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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,917
Members
418,253
Latest member
TheJackal26

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
Top