Finding values in selected Ranges???? 1

[ribhead]

I need a little (lot) help here with this code. I'm not sure why I'm getting a 400 alarm when I run this. I'm trying to count the number of values in a range that I think I defined but I'm not sure. Then I want to put that variable in a message box. I borrowed some of this from a book that I found but it's an older version of VBA. I think it's for a Commadore 64 sp??

Sub find_value()
Dim myrange As Range
Dim count As Integer
count = 0
latrow = Cells(1, 1).End(xlDown).Row
myrange = Range(Cells(1, 1), Cells(lastrow, 1)).Address
For Each C In myrange
If C.Value = 5 Then
count = count + 1
End If
Next C
MsgBox "There are " & C & " entries found"
End Sub

I may not be very smart but I'm sure wirey!!!!

 

[TonyJollans]

Hi ribhead,

Several things wrong here .

You have myrange dimmed as a range but you are trying to set it to a string, try this instead:

Set

 myrange = Range(Cells(1, 1), Cells(lastrow, 1))

 ' .Address

Also you have a typo (here or in the real code?) when you set latrow instead of lastrow. If you had

Option Explicit

coded at the top of your module (before the first sub) the compiler would catch this as well as your lack of declaration for both lastrow and C.

And finally, your msgbox at the end should use count, not C.

Enjoy,
Tony

 

[ribhead]

Thanks a lot Tony. I am actually kind of excited because I actually understand what I did wrong!! I believe I am starting to understand how some of this stuff is supposed to work but definitely not all. When do I need the
.Address at the end of an object? A range is an object right?

I may not be very smart but I'm sure wirey!!!!

 

[TonyJollans]

Hi ribhead,

I wouldn't worry about understanding it all, I don't think I do but I get by, and I do know how to find out what I don't know.

A range, as you say, is an object - one or more cells; from the range object you have access to all the properties - the row, the column, the contents, the colour, etc. The address is just one of the properties - a string like "$A$1" or "$C$3:$X$123" - a way of referencing the cell. The answer to your question as to when you need the address is probably 'not often'; I find it of limited use in code - it is usually easier to work with the numeric row and column properties.

Enjoy,
Tony

 

[ribhead]

Thanks Tony I have one more question. I am going one step further with this program and I want to do another For each loop and hide all the rows that aren't equal to the value. I know my programming skill are very crude but I still have a hard time understanding when I can do certain things.

This is what I have.

Private Sub CommandButton1_Click()
Dim myrange As Range
Dim count As Integer
mynum = Val(TextBox1.Value)
count = 0
lastrow = Cells(1, 1).End(xlDown).Row
Set myrange = Range(Cells(1, 1), Cells(lastrow, 1)) 'Address
For Each c In myrange
If c.Value = mynum Then
count = count + 1
End If
Next c
UserForm1.Hide
MsgBox "There are " & count & " entries found matching this description" & vbCr & "do you wish to view these now?", vbYesNo, "Search Results"
If vbYes Then
For Each c In myrange
If c <> mynum Then
EntireRow.Hide
End If
Next c
Else
End If
End Sub

I guess it doesn't know what row to hide so it gives me an Object Required alarm so how do I tell it the object is the cell that is currently being checked?

I may not be very smart but I'm sure wirey!!!!

 

[TonyJollans]

Hi ribhead,

Two problems with the line EntireRow.Hide

1. EntireRow is a property of a Range (which gives you another Range (the entire row)) and it needs a Range to apply to - that is what your Object Required error means. You need to say C.EntireRow... to mean the entire row to which cell C belongs.

2. When you have the (entirerow) Range you can then apply Properties and Methods to it - that is what your Hide is trying to do, but there isn't a Hide method; you must set the Hidden property true instead, like this ..

C.EntireRow.Hidden = True

I hope that all makes sense. Post back if not.

Enjoy,
Tony

 

[ribhead]

I originally had c.entirerow.hidden but I didn't have the
= True so I thought I was way off. Some of this is starting to make sense but I still have a hard time with structures. Is this correct Object.Property.Method ??

I try using the Object Browser for help but still get a little confused between Properties and Methods. Thanks for taking time to help me and explain things. I appreciate it and you definitely get a star from me.

I may not be very smart but I'm sure wirey!!!!

 

[TonyJollans]

Hi ribhead,

This is a little complicated, and the answer is 'it depends'.

There are (loosely) two ways to interact with an object: you can read (and generally write) its properties which are, in effect, variables belonging to the object; or you can apply a method to it which, in effect, means running some code belonging to the object. Both use the same (dot) syntax. There is also the &quot;!&quot; (bang) syntax which you sometimes have to use but let's leave that for now.

The value of some Properties is an(other) Object (in the jargon, the Property returns an Object). When this is the case, Properites and Methods of this new Object can be referenced by simply appending another dot and the appropriate name. This can go on (almost) indefinitely which is why you sometimes get long strings of Object.Property.Property.Property....

You are (again, loosely) right in your example of Object.Property.Method. (more fully it's Object<.Property<.Property<. ... >>><.Method>). When you code a method you can pass it parameters (named or positional which follow either in parentheses or space-separated depending on the form of the full statement) but you cannot tag any more properties or methods on to it.

In the case of your code, EntireRow is, in effect, an object variable belonging to the cell (C) which has been set to the row which the cell is on so when you code the two together as C.Entirerow you have another object. Hidden is a Property of this new Object and is a Boolean variable. When you tag this on the end to get C.Entirerow.Hidden you have a variable name which you can treat like any other and, in your case you set it to True.

Once again, I hope you can follow this.

Enjoy,
Tony