Processing every other column in a range with VBA

[Legacy 54891]

Hi

I am trying to write a user defined function to count how many cells contain either "A", "B" or "C" within a given range, but only do this for the odd columns in the range (even if the first column of the range is for example D (i.e. 4) this would still be counted as odd as it is the first column in the range.)

For example =CountAtoC(B1:E2) would return 3 if the following data is stored in the range B1:E2 because there are three cells containing "A", "B" or "C" in the odd columns i.e. 1st and 3rd columns.

B C A E
A F D C

So far I have got the following

Function CountAtoC(SelectedRange)

    CountAtoC = 0

    For Each Cell In SelectedRange
        If Cell.Value = "A" Then CountAtoC = CountAtoC + 1
        If Cell.Value = "B" Then CountAtoC = CountAtoC + 1
        If Cell.Value = "C" Then CountAtoC = CountAtoC + 1
    Next Cell
    
End Function

but I am struggling where to go next. I suppose I need to figure out which column the current cell being processed is in, but I am not sure how to do this. If I could also get the cell reference of the first (upper left) cell then I would have enought information.

Any help would be greatfully received.

Thankyou.

 

[VoG]

This will return the address of the top left cell

MsgBox Range(SelectedRange)(1, 1).Address(False, False)

 

[Legacy 54891]

This will return the address of the top left cell

MsgBox Range(SelectedRange)(1, 1).Address(False, False)

Many thanks for that. Could you please explain what the (false, false) part does?

Do you know how to get the address of the current cell being processed in my For-Next loop?

 

[VoG]

The False,False just returns a relative address like B1. If omitted it would return $B$1.

Try Cell.Address(False,False) to get the address of the cell being processed.

 

[Legacy 54891]

The False,False just returns a relative address like B1. If omitted it would return $B$1.

Try Cell.Address(False,False) to get the address of the cell being processed.

Cheers, that makes sense. I will have a go now. Rather than the full address is it possible just to extract the column or will I have to do some string manipulation on the address?

 

[VoG]

oops! If SelectedRange is a range like B1:E2 rather than a string containing the range "B1:E2" it should be

MsgBox SelectedRange(1, 1).Address(False, False)

You can get the column number using

MsgBox SelectedRange(1, 1).Column

and similarly with a cell

MsgBox Cell.Column

 

[Legacy 54891]

Thanks, I'm off to try all this now!

 

[Legacy 54891]

Many, many thanks VoG !

I've got it doing exactly what I want now.

Here is the code

Function CountAtoC(SelectedRange)
    columnstart = SelectedRange(1, 1).Column
    CountAtoC = 0
    For Each cell In SelectedRange
    CurrentColumn = cell.Column
        If ((columnstart Mod 2) = 0 And (CurrentColumn Mod 2) = 0) Or ((columnstart Mod 2) = 1 And (CurrentColumn Mod 2) = 1) Then
            If cell.Value = "A" Then CountAtoC = CountAtoC + 1
            If cell.Value = "B" Then CountAtoC = CountAtoC + 1
            If cell.Value = "C" Then CountAtoC = CountAtoC + 1
        End If
        Next cell
End Function

I have done some modulo arithmetic to look for odd and even values, a bit messy but iw works.

 

[VoG]

You are more than welcome. It is a pleasant change to receive a request for a nudge in the right direction rather than "I urgently need a marco {sic} to do summat really complicated".

 

[Legacy 54891]

You are more than welcome. It is a pleasant change to receive a request for a nudge in the right direction rather than "I urgently need a marco {sic} to do summat really complicated".

I pefer to get a 'nudge' as that way I will understand better for the next time. Cheers.