Run time error 1004 - VBA Vlookup

[marshy3300]

Howdy,

I have this code

With Sheets("CDG Current")
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
With .Range("N2:N" & LastRow)
.Formula = _
"=IF(K2>VLOOKUP(RC4,'CDG MinMax'!R2C1:R6666C23,5,0),"""",IF(E2=E1,IF(SUMIF($E$1:$E1,E2,$N$1:$N1)+K2<0,M2,0),M2))"
'"=IF(K2>500,"""",IF(E2=E1,IF(SUMIF($E$1:$E1,E2,$N$1:$N1)+K2<0,M2,0),M2))"
.Value = .Value
End With
End With

And getting the runtime error on the vlookup line. Not sure why. if I change it to the >500 as below that it runs fine but i want it to lookup another sheet and grab that value.

Any help would be appreciated.

THANKS!

 

[Trebor76]

Hi marshy3300,

Forgetting about VBA for the moment, what is the actual worksheet formula you're trying to convert?

Robert

 

[marshy3300]

Its basically a book that i dump in my overdue purchases to work out priorities to get made. On hand stock + on the water - allocated gives balance (k2).
N2 works out what can be made (overall minus on water) total whats allocated (same product can be listed multiple times) and if everything that balance is less than 500 it will spit out a quantity to make until that 500 total is reached.
That all works fine. What i amended was a lookup to change the 500 to a predetermined minimal stock holding on another page (which a seperate macro imports from a file) which spits the error.

 

[Trebor76]

You didn't answer the question - what is the actual formula you want to replicate in VBA?

 

[marshy3300]

Hi Trebor,

I replaced the RowColumns with actual cell references (A4) and seems to be working. Thanks for looking into the thread though!