Error in Formula with Named Range

[BigNate]

Hello Everyone,

In my last line of code:

Selection.Value = "=VLOOKUP(A5,Completelist," & HLForm & ",0),0)"

I get the following error:
"Run time error '1004'
Application-defined or object-defined error"

My entire code is as follows. Can someone please tell me what I am doing wrong and what is returning the error?

Sub MinuteLookup()


Dim WB As Workbook
Set WB = ThisWorkbook
Dim WSPPCP As Worksheet
Set WSPPCP = WB.Worksheets("ProductivityPivotCP")
'the following is the range for the vlookup
WSPPCP.Range("A3:M149").Name = "CompleteList"
'the following is the range for the hlookup
WSPPCP.Range("D1:M2").Name = "HLookRange"




Range("E4").Select
Selection.Formula = "=VLOOKUP(A5,Completelist,4,0)"


Range("E5").Select
Selection.Value = "HLOOKUP(E1,HLookRange,2,0)"
HLForm = Range("E5").Value
Range("E6").Select
Selection = HLForm


Range("E7").Select
Selection.Value = "=VLOOKUP(A5,Completelist," & HLForm & ",0),0)"




End Sub

 

[mperrah]

Sub MinuteLookup()

Dim WB As Workbook
Dim WSPPCP As Worksheet

Set WB = ThisWorkbook
Set WSPPCP = WB.Worksheets("ProductivityPivotCP")

'the following is the range for the vlookup
WSPPCP.Range("A3:M149").Name = "CompleteList"

'the following is the range for the hlookup
WSPPCP.Range("D1:M2").Name = "HLookRange"

Range("E4").Formula = "=VLOOKUP(A5,Completelist,4,0)"

Range("E5").Value = "=HLOOKUP(E1,HLookRange,2,0)" ' your code was missing an equal sign here
HLForm = Range("E5").Value
Range("E6").Value = HLForm

Range("E7").Value = "=VLOOKUP(A5,Completelist," & HLForm & ",0),0)"

End Sub

not sure of other errors without test data

 

[mperrah]

Sub MinuteLookup()

Dim WB As Workbook
Dim WSPPCP As Worksheet

Set WB = ThisWorkbook
Set WSPPCP = WB.Worksheets("ProductivityPivotCP")

'the following is the range for the vlookup
WSPPCP.Range("A3:M149").Name = "CompleteList"

'the following is the range for the hlookup
WSPPCP.Range("D1:M2").Name = "HLookRange"

Range("E4").Formula = "=VLOOKUP(A5,CompleteList,4,0)"

Range("E5").Value = "=HLOOKUP(E1,HLookRange,2,0)"
HLForm = Range("E5").Value
Range("E6").Value = HLForm

Range("E7").Value = "=VLOOKUP(A5,CompleteList,HLForm,0)" ' removed  ,0) from end, Needed? 

End Sub

some of the CompleteList had list (lowercase l)
now it gets a Name? error

 

[BigNate]

mperrah,

Thanks for your time. Im still getting the same error message. I know the reason for why I am getting the error, but I just don't know the resolution for getting past the error. The reason is that I am not correctly inserting the column index number for my vlookup. My column index number is being determined by an hlookup.

To provide more detail, I know a vlookup needs to be:
Vlookup(lookup value, table to do lookup in, column with desired answer)

It is the part in red above that I cannot get. I can find the column using a hlookup (separately), but when I go to substitute my hlookup function in for the red portion above, it always returns an error.

Do you know how I would insert the hlookup function in my first post to be used as the column number in my vlookup function? Again, I want to do an hlookup to determine the column count of the vlookup.

Thanks again!

 

[mperrah]

There are parts of your code referring to itself causing errors
what are you trying to accomplish with the formula?
We can most likely do it all in vba and not need the formula.
please provide sample data before on one sheet and after on a second sheet plus your goal spelled out.

also strange that the formula is in the middle of the look up ranges.
I would think to set a result cell apart from the calculation area would be more efficient

 

[mperrah]

It looks like the hlookup is being replaced with the value in E6,
but I think we need the address of the hlookup result, not the value.
again, sample data would speed up a solution