VBA object required error with Range function

Question

I have a piece of VBA code that looks up value from another excel to create a True/False flag and based on the flag, I have set two different char values. I get 'object required' error in the following code. Can some one please explain why?

Sub test()

  Dim part1 As String
  Dim part2 As String
  Dim a As Range

  part1 = "=ifna(INDEX('DRG and Zip Summaries'!$A$10:$A$58,MATCH('DRG Summary Target'!F2 ""x_x_x"""
  part2 = ",'DRG and Zip Summaries'!$C$10:$C$58,0)),'FALSE')"

With Range("A2:A183").FormulaArray = part1
                                  .Replace """x_x_x""", part2
End With

For Each a In Range("A2:A183")
 If a.Value = "FALSE" Then
        Range("B" & a.rownum) = Chr(168)
 Else:  Range("B" & a.rownum) = Chr(254)
 End If
Next

 End Sub

Which line gives you an error? Range("B" & a.rownum) is incorrect, it should be Range("B" & a.Row). Also, you should qualify your referecnes, for instance Workbooks("Workbook Name").Sheets("Sheet Name").Range("A2:A183"). — Kyle
– Kyle, Commented Sep 28, 2016 at 18:37

Werrf · Accepted Answer · 2016-09-28 18:40:54Z

1

Don't place your formula in the WITH declaration. The WITH declaration is just supposed to identify the object to work with, not make any changes like putting values in it. Use:

With Range("A2:A183")
    .FormulaArray = part1
...

etc.

I'm not sure why you're using the "part1" and "part2" structure either. If it's just because the formula was getting too long, you can break lines using an underscore character, thus:

formula=""=ifna(INDEX('DRG and Zip Summaries'!$A$10:$A$58," & _
    "MATCH('DRG Summary Target'!F2",'DRG and Zip Summaries'!" & _
    "$C$10:$C$58,0)),'FALSE')"

The underscore will ensure these three lines are all processed as a single line.

answered Sep 28, 2016 at 18:40

Werrf

1,2447 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Akshata T Over a year ago

I removed the 'with' section and used the code as suggested with & _ . The error now says 'Application defined or object defined error'

Werrf Over a year ago

There was a glitch in my formula, with the double-quotes before the =. It should be formula="=ifna(Index( .... etc. Also, make sure you're closing the " at the end of each line and including the &, then reopening the " at the beginning of the next line.

Akshata T Over a year ago

Yep, I had noted that and wrote the formula accordingly so it looks like following code: Sheet("Sheet5").Range("A2:A183").FormulaArray = "=ifna(INDEX('DRG and Zip Summaries'!$A$10:$A$58," & _ "MATCH('DRG Summary Target'!F2,'DRG and Zip Summaries'!" & _ "$C$10:$C$58,0)),'FALSE')"

Werrf Over a year ago

Use double-quotes around "False" at the end, or remove the single quotes. Excel doesn't know what to do with 'FALSE', which is where the error is probably coming from.

Collectives™ on Stack Overflow

VBA object required error with Range function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related