Object Required Error - really simple bit of code...

Claire_Brummell

Board Regular
Joined
Sep 29, 2004
Messages
129
Hey,

Trying to get a simple sub written.

- When it's run I need to record the value of the active cell.
- It then needs to check to see if I want to make this a 'key channel'
- If the answer's yes I want it to cycle through all the rows making the K column = 1 if the A column equals the value of the initial active cell.

This is the sub:

Code:
Sub Set_Key_Channel()

Dim current_row As Long
Dim new_key_channel As String

    new_key_channel = Active_Cell.Value
    Response = MsgBox("Do you want to set " & new_key_channel & " as a key channel for " & ActiveSheet.Name & "?", vbYesNo)
    If Response = vbYes Then
        For current_row = 2 To ActiveSheet.Rows.Count
            If Cells(current_row, 1) = new_key_channel Then
                Cells(current_row, 11) = "1"
            End If
        Next current_row
    Else
   
    End If

End Sub

But I'm getting an object required error on the first line after my variable decs - any ideas?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
I see an error right off the bat.

You have:
new_key_channel = Active_Cell.Value

It should be:
new_key_channel = ActiveCell.Value
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,803
Is there actually an active cell ie. is the cell selected? This "new_key_channel As String" should likely be "new_key_channel As Long" and this "Dim current_row As Long" should likely be "Dim current_row As Integer" HTH. Dave
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,912
Office Version
  1. 365
Platform
  1. Windows
Dave,

Why do you say that "new_key_channel" should be declared as Long instead of String? I see nothing in the post that indicates what values might be stored in "new_key_channel". And from I can see, that would not be causing the problems they are having.

Also, "currnet_row" should be declared as Long, like they have it, and not Integer. Why? Integers can only go up to 32,767 and Excel can hold up to 65,536 rows. So whenever declared row variables, it is always better to declare them as Long instead of Integer (unless you can be absolutely certain that they will NEVER use more than 32,767 rows).
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

I would go so far as saying that, in this sub, current_row should never be declared as an integer, as the following code line:

Code:
For current_row = 2 To ActiveSheet.Rows.Count

will result in an overflow error every time if current_row is declared as an integer.

Richard
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,803
JM13...It's early am and you are right of course on both points. I didn't like "Cells(current_row, 1) = new_key_channel " as I assumed "Cells(current_row, 1)" was a value by the thread intro and I don't support comparing apples to oranges. I see also that column "K" is actually ending up as text ("1")... not sure if this is right? The use of long is necessary of course if there are plenty of lines but integers require less "space" if plenty of lines aren't a concern hence the "should likely be" part of the reply. You have changed your moniker (JMiskey? previous). Thanks for your comments. Your post was unknown to me at the time of my posting. I would have considered this solved had I known. Have a nice day. Dave
edit: good point Richard. I hadn't considered that.
 

Claire_Brummell

Board Regular
Joined
Sep 29, 2004
Messages
129
Well, I just got back from lunch and I see you guys have been busy discussing without me! ;)

OK, I'll try and answer each point individually!

jm14 - thanks, you're right - problem solved!

NdNovicHlp - the others are correct. Yes there is a current cell selected and it is the contents of that cell (which is text) which I want to store in new_key_channel. Also I've defined current_row as long to avoid overflow errors and integers aren't big enough :)

jm24 - yep you're right on your second point see my comments above.

Richard - yep - again see above.

NdNovicehlp - new_key_channel has a text string in it which is taken from column A, therefore I want to compare the text string from the activecell to all the other cells in column A and then set the K column accordingly. The K colulmn is simply an indicator which is why I'm making it a text 1 not a value of 1 as it's not needed for calculations.

Thanks for all your input guys - much appreciated!! :D
 

Forum statistics

Threads
1,140,916
Messages
5,703,158
Members
421,278
Latest member
16cwilliams

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
Top