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

[Claire_Brummell]

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:

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?

 

[Joe4]

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]

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]

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]

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:

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]

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]

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