Excel Object Required Error when Running Macro

Question

I am trying to create a macro such that if a given cell in the range L2:L318 contains a particular value, the cell in column H in the same row is modified to a new string.

At this point, I have what appears to be something passable, but I get an 'Object Required' error when it runs. My code is as follows:

Sub Macro2()

Dim rng As Range
Dim cell As Range
Set rng = Worksheet.Range("L2:L318")
Dim i As Integer


For Each cell In rng
    If cell.Value("902.4") Then
    i = i + 1
    Sheet.Cells(8, i).Value = "Text"


    Else
    i = i + 1

    End If

Next cell


End Sub

I'll admit I haven't the slightest idea what I'm doing, and there's at least a moderate chance my code is complete nonsense. Can anyone explain to me what I'm doing wrong? Any help would be appreciated - I've literally never touched Excel macros before today!

@BrianMStafford you're correct - I missed that! That being said, it still returns the 'Object Required' error. — Paige
– Paige, Commented Feb 26, 2019 at 19:22
Where is Worksheet defined? Is that supposed to be ActiveSheet? Same with Sheet. I'd suggest putting Option Explicit at the top of the module and correcting all the errors it flags. — Comintern
– Comintern, Commented Feb 26, 2019 at 19:24
set rng = Worksheets("your worksheet name").Range("L2:L318") — Guest
– Guest, Commented Feb 26, 2019 at 19:26

sous2817 · Accepted Answer · 2019-02-26 19:25:49Z

2

This is a great start! You've got a few things off, but for the most part you weren't too far off. Keep in mind there are a ton of different ways to approach this, but since you're learning, I took your approach and made it work. See if this gets you close to what you're after:

Sub Macro2()

Dim rng As Range
Dim cell As Range
Set rng = Range("L2:L318")

For Each cell In rng
    If cell.Value = 902.4 Then Cells(cell.Row, 8).Value = "Text"
Next cell

End Sub

Take a look at what I posted and see if you can make heads or tails of it. As you can see, it's not too far off what you had, just cleaned up a little and fixed the syntax.

Keep in mind, this macro will run on the currently active sheet. If you need it to run on a specific sheet, you'll have to qualify your range reference a bit better...something like: Worksheets("your worksheet name").Range("L2:L318")

answered Feb 26, 2019 at 19:25

sous2817

3,9602 gold badges37 silver badges37 bronze badges

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

2 Comments

Paige Over a year ago

Oh my gosh it works. I've been staring at this for an hour - you're a life saver. Correct me if I'm wrong - doesn't this effectively render i pointless?

sous2817 Over a year ago

@PaigeJaclyn yep, that's why I took it out :). If it helped, feel free to up vote and /or accept it as a solution so others know that it worked.

Collectives™ on Stack Overflow

Excel Object Required Error when Running Macro

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related