Probs with For Each statement

[westicle47]

I'm trying to check the first character of each cell in a given range. I've tried declaring the variable 'z' as a range but I get the error message "Object required".

Any ideas?

'Insert the DivisionTypes
            DivisionTypeRange = Range("B" & CurrentCellRow - ItemCount, "B" & CurrentCellRow - 1)
            
            
            
            For Each z In DivisionTypeRange
                If z.characters(1, 1) = "T" Then
                    DivisionTypes = DivisionTypes + 1
                End If
            Next
            
            Cells(CurrentCellRow, 2) = DivisionTypes

 

[xlbo]

a RANGE is an OBJECT and therefore must be SET
change:
DivisionTypeRange = Range("B" & CurrentCellRow - ItemCount, "B" & CurrentCellRow - 1)

to

Set DivisionTypeRange = Range("B" & CurrentCellRow - ItemCount, "B" & CurrentCellRow - 1)


You can also use
IF Left(z,1) = "T"

instead of the characters function


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi westicle47,

Excel doesn't have a Characters Collection - you need to use the Left Function to look at the first character of a Cell ..

[blue]

            For Each z In DivisionTypeRange
                If Left(z,1) = "T" Then
                    DivisionTypes = DivisionTypes + 1
                End If
            Next

[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

erm....Tony.....I know this is a monday morning but I'm pretty sure that whilst Excel doesn't have a characters COLLECTION, it certainly has a characters PROPERTY and westicle's code is accessing the property, NOT the collection

Range.characters(start, length)

is perfectly valid syntax - it's just that left(Range,1) is quicker to write - I think the issue here is SETTING the range rather than anything else

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[westicle47]

Thanks guys. That works perfectly!

 

[TonyJollans]

Hi Geoff,

'tis true, there is a Characters Property and it could be used, but to get the text you would need to use the Text Property of the Characters Object which the Property returns ..

[blue]If z.Characters(1,1).Text = "T" Then ...[/blue]

- definitely long-winded

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[TonyJollans]

Hi Geoff,

Sorry, I was a bit ungracious in my response this morning. You were perfectly right to pick me up; I hadn't read the code properly - I had just seen "characters" (with a lower case "c" implying that the editor hadn't recognised it) and jumped to the conclusion that westicle47 was trying to use Word's Collection in Excel, and responded without thinking it through. Sorry.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

Tony - really no need to apologise - you are a scholar and a gentleman - my response was half in jest (which didn't come across too well) so I took yours in the same light

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.