CODEWITHSUNDEEP
vbaexcel
Hicham Benhima
Hicham Benhima

Reputation: 121

Runtime error 1004 Range Formula

I spent this afternoon trying to compile a macro but I'm stuck on a runtime error :

Application-Defined or Object-defined error.

Here's my code :

Sub MajPO()


Dim i As Integer

Dim FromRStart, FromREnd, ToRStart, ToREnd
Dim Technology(18) As String
Technology(0) = "ADSL"
Technology(1) = "ADTRAN"
Technology(2) = "ADVA"
Technology(3) = "AGW HUAWEI"
Technology(4) = "CISCO"
Technology(5) = "CSI DWDM HUAWEI"
Technology(6) = "IP & IP/VPN REPAIR"
Technology(7) = "JUNIPER"
Technology(8) = "MEGAPAC"
Technology(9) = "MICROWAVE HUAWEI"
Technology(10) = "POWER"
Technology(11) = "ROP HOUSING"
Technology(12) = "SDH ERICSSON"
Technology(13) = "SDH MARCONI"
Technology(14) = "SOP14XX"
Technology(15) = "SYNCRO-GILLAM"
Technology(16) = "VDSL1"
Technology(17) = "VDSL2"

For i = 0 To 18
  Worksheets("FromRepair").Activate
    FromRStart = Application.Match(Technology(i), Range("A:A"), 0)
    FromREnd = Application.Match(Technology(i) & " Total", Range("A:A"), 0)
Worksheets("MissingPO").Activate
    ToRStart = Application.Match(Technology(i), Range("A:A"), 0)
    ToREnd = Application.Match(Technology(i) & " Total", Range("A:A"), 0)

myRange = ("K" & FromRStart & ":L" & FromREnd)
Range("O" & ToRStart).Formula = "=IFNA(VLOOKUP(B6;FromRepair!" & myRange & ";11;0)"

Next



End Sub

And the error is in the following line : 

Range("O" & ToRStart).Formula = "=IFNA(VLOOKUP(B6;FromRepair!" & myRange & ";11;0)"

Does anyone of you know how I can solve this problem?

Upvotes: 1

Views: 160

Answers (2)

Hicham Benhima
Hicham Benhima

Reputation: 121

As @Vasmi said there was a missing bracket in my formula, so the resolution was : 

Range("O" & ToRStart).Formula = "=IFNA(VLOOKUP(B6,FromRepair!" & myRange & ",11,FALSE),0)"

instead of 

Range("O" & ToRStart).Formula = "=IFNA(VLOOKUP(B6,FromRepair!" & myRange & ",11,FALSE)

Upvotes: 0

R3uK
R3uK

Reputation: 14537

As @Vamsi said your are missing a closing bracket in your formula.

Furthermore, I'd add that you didn't specify in the VLOOKUP if you want an exact or approximate match. As this argument is by default set to approximate, I'd suggest that you take the habit to set it to match your purpose.

So I'd propose this :

Range("O" & ToRStart).Formula = "=IFNA(VLOOKUP(B6,FromRepair!" & myRange & ",11,FALSE),0)"

Upvotes: 1

Related Questions