RANGE .VALUE & .FORMULAR1C1 Will Not Accept - 1004 'Range of Object Error'

S

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
Good Morning Experts,

I have used the following in many of the macros I have built, and for some reason, this particular procedure doesn't like my code and I cannot figure out why. This time I've used a different looping method and added multiple variables. I've highlighted the errors I receive below. .Value & .FormulaR1C1 doesn't want to work...

In the past I've always been able to use:

Range("A1").Value = "=(whatever code,1,0)"

and it always just input the formula in my designated range... But not now. Can someone enlighten me please?

Code: 
Sub Step10a()


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim row As Long
Dim code1 As String
Dim code2 As String
Dim code3 As String
Dim match1 As String
Dim match2 As String
Dim match3 As String


row = 2


match1 = ""
match2 = ""
match3 = ""


Range("AR").[COLOR=#ff0000][B]Value [/B][/COLOR]= "=VLOOKUP(U2,COMAT!$A$2:$T$50000,16,0)"  [COLOR=#008000]'GIVES 1004 ERROR[/COLOR]
Range("AS").[COLOR=#ff0000][B]FormulaR1C1 [/B][/COLOR]= "=VLOOKUP(U2,COMAT!$A$2:$T$50000,17,0)" [COLOR=#008000]'GIVES 1004 ERROR[/COLOR]
Range("AT").FormulaR1C1 = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,18,0)"
Range("AU").FormulaR1C1 = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,19,0)"
Range("AV").FormulaR1C1 = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,20,0)"


Do Until Range("AL" & row).FormulaR1C1 = ""


code1 = Range("AW" & row).Value = "VLOOKUP(C2,IMP.SPL!$A$2:$D$44,2,0)"
code2 = Range("AX" & row).Value = "VLOOKUP(C2,IMP.SPL!$A$2:$D$44,3,0)"
code3 = Range("AY" & row).Value = "VLOOKUP(C2,IMP.SPL!$A$2:$D$44,4,0)"


If code1 = Range("AR" & row).Value Then match1 = "MATCH"
If code1 = Range("AS" & row).Value Then match1 = "MATCH"
If code1 = Range("AT" & row).Value Then match1 = "MATCH"
If code1 = Range("AU" & row).Value Then match1 = "MATCH"
If code1 = Range("AV" & row).Value Then match1 = "MATCH"


If code2 = Range("AR" & row).Value Then match2 = "MATCH"
If code2 = Range("AS" & row).Value Then match2 = "MATCH"
If code2 = Range("AT" & row).Value Then match2 = "MATCH"
If code2 = Range("AU" & row).Value Then match2 = "MATCH"
If code2 = Range("AV" & row).Value Then match2 = "MATCH"


If code3 = Range("AR" & row).Value Then match3 = "MATCH"
If code3 = Range("AS" & row).Value Then match3 = "MATCH"
If code3 = Range("AT" & row).Value Then match3 = "MATCH"
If code3 = Range("AU" & row).Value Then match3 = "MATCH"
If code3 = Range("AV" & row).Value Then match3 = "MATCH"


If match1 = "MATCH" Then
    Range("AZ" & row).Value = "MATCHES"
Else
    If match2 = "MATCH" Then
        Range("AZ" & row).Value = "MATCHES"
    Else
        If match3 = "MATCH" Then
            Range("AZ" & row).Value = "MATCHES"
        Else
            Range("AZ" & row).Value = "NO MATCHES"
        End If
    End If
End If


row = row + 1


Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Sphinx404 said:
Code: 
Range("AR").[COLOR=#ff0000][B]Value [/B][/COLOR]= "=VLOOKUP(U2,COMAT!$A$2:$T$50000,16,0)"  [COLOR=#008000]'GIVES 1004 ERROR[/COLOR]
Range("AS").[COLOR=#ff0000][B]FormulaR1C1 [/B][/COLOR]= "=VLOOKUP(U2,COMAT!$A$2:$T$50000,17,0)" [COLOR=#008000]'GIVES 1004 ERROR[/COLOR]
Range("AT").FormulaR1C1 = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,18,0)"
Range("AU").FormulaR1C1 = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,19,0)"
Range("AV").FormulaR1C1 = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,20,0)"
Click to expand...
Where are the row numbers on those "ranges"?
 
Upvote 0
The way you have written the formula, you are not returning the value nor the formula in R1C1 format. Rather, you are just returning the formula in Excel format.
You also forgot the row reference in your formula.
So try this:
Code: 
Range("AS[COLOR=#ff0000][B]2[/B][/COLOR]").[COLOR=#ff0000][B]Formula [/B][/COLOR]= "=VLOOKUP(U2,COMAT!$A$2:$T$50000,17,0)"
 
Upvote 0
Also in addition to Ricks suggestion the 2nd formula isnt in R1C1 style so just use .Formula not .FormulaR1C1
 
Upvote 0
Rick,

I've been looking at this since last Friday trying to figure out what in the heck is going on.... This is embarrassing. Thank you.
 
Upvote 0
Thank you guys.... sigh. Chalk this one up to likely the number 1 bonehead questions of the day. However, I did learn something new. @Joe4: So I can just input .Formula? I don't need to input .Value? I can't imagine they are the exactly the same?
 
Last edited:
Upvote 0
Joe4 said:
The way you have written the formula, you are not returning the value nor the formula in R1C1 format. Rather, you are just returning the formula in Excel format.
You also forgot the row reference in your formula.
So try this:
Code: 
Range("AS[COLOR=#ff0000][B]2[/B][/COLOR]").[COLOR=#ff0000][B]Formula [/B][/COLOR]= "=VLOOKUP(U2,COMAT!$A$2:$T$50000,17,0)"
Click to expand...

@Joe4

Outside of inputting the "2" in all of the row designations that I had forgotten, do you see something in this code that would prevent it from looping?

I cannot get anything to occur after it inputs these formulas... I had this working at home and now I can't figure out what I'm doing wrong....

Code: 
Range("AR2").Value = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,16,0)"
Range("AS2").Value = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,17,0)"
Range("AT2").Value = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,18,0)"
Range("AU2").Value = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,19,0)"
Range("AV2").Value = "=VLOOKUP(U2,COMAT!$A$2:$T$50000,20,0)"

Anything stick out to you from the original code?
 
Upvote 0
Why are you using ".Value" instead of ".Formula", like I mentioned?

If it is still not working, please post your most current code, so we can see all your changes.
Also, can you explain exactly what it is you are trying to do with this code?
 
Upvote 0
Code: 
Dim row As Long
Dim code1 As String
Dim code2 As String
Dim code3 As String


Dim match1 As String
Dim match2 As String
Dim match3 As String


row = 2


Range("AD2").Value = "=VLOOKUP(C2,IMP.SPL!$A$2:$D$44,2,0)"
Range("AE2").Value = "=VLOOKUP(C2,IMP.SPL!$A$2:$D$44,3,0)"
Range("AF2").Value = "=VLOOKUP(C2,IMP.SPL!$A$2:$D$44,4,0)"


Range("AG2").[B]Value [/B]= "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,16,0),)"
Range("AH2").Value = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,17,0),)"
Range("AI2").Value = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,18,0),)"
Range("AJ2").Value = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,19,0),)"
Range("AK2").Value = "=IFNA(VLOOKUP(U2,COMAT!$A$2:$T$26694,20,0),)"


Range("AD2:AK2").Select
Selection.AutoFill Destination:=Range("AD2:AK25000")


Do Until Range("AD" & row).Formula = ""


match1 = ""
match2 = ""
match3 = ""


[B][COLOR=#ff0000]code1 = Range("AD" & row).Value [/COLOR][COLOR=#008000]'Run-time Error 13[/COLOR][COLOR=#ff0000][/COLOR][/B]
code2 = Range("AE" & row).Value
code3 = Range("AF" & row).Value


If code1 = Range("AG" & row).Value Then match1 = "MATCH"
If code1 = Range("AH" & row).Value Then match1 = "MATCH"
If code1 = Range("AI" & row).Value Then match1 = "MATCH"
If code1 = Range("AJ" & row).Value Then match1 = "MATCH"
If code1 = Range("AK" & row).Value Then match1 = "MATCH"


If code2 = Range("AG" & row).Value Then match2 = "MATCH"
If code2 = Range("AH" & row).Value Then match2 = "MATCH"
If code2 = Range("AI" & row).Value Then match2 = "MATCH"
If code2 = Range("AJ" & row).Value Then match2 = "MATCH"
If code2 = Range("AK" & row).Value Then match2 = "MATCH"


If code3 = Range("AG" & row).[B]Value [/B]Then match3 = "MATCH"
If code3 = Range("AH" & row).Value Then match3 = "MATCH"
If code3 = Range("AI" & row).Value Then match3 = "MATCH"
If code3 = Range("AJ" & row).Value Then match3 = "MATCH"
If code3 = Range("AK" & row).Value Then match3 = "MATCH"


If match1 = "MATCH" Then
    Range("AL" & row).Value = "MATCHES"
Else
    If match2 = "MATCH" Then
        Range("AL" & row).Value = "MATCHES"
    Else
        If match3 = "MATCH" Then
            Range("AL" & row).Value = "MATCHES"
        Else
            Range("AL" & row).Value = "NO MATCHES"
        End If
    End If
End If
row = row + 1


Loop


End Sub

I've got it sorted out for the most part. Please note that I didn't ignore your previous reply on changing the ".Value" to ".Formula" The only reason I didn't change it yet is because thus far, the code runs the way it should minus the run-time error I'm getting. I'm guessing I'm getting that error because it hits a blank row at the bottom.

In any case, the code is mostly figured out. The question I have refers to the 2 BOLDED values above: Certainly .Value can work, but it might not be appropriate in most cases. I'll have to do some further reading. Thank you Joe4
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Unable to set the FormulaArray property of the Range class
Replies
8
Views
581
RoryA
RoryA
jalrs
  • Solved
Loop Working but gives me a run Time Error 1004
2
Replies
10
Views
1K
jalrs
jalrs
jalrs
  • Solved
PivotTable not Refreshing
2 3 4
Replies
36
Views
2K
Alex Blakenburg
A
E
  • Solved
Index and Match - Wrong Value?
Replies
5
Views
180
Peter_SSs
Peter_SSs
W
  • Question
Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defined error)
Replies
3
Views
1K
winz
W

Forum statistics

Threads
1,215,637
Messages
6,125,965
Members
449,276
Latest member
surendra75

Share this page

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
Back
Top