VBA Dynamic No. Of Column Headers

ir121973 · Feb 20th 2014

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to count values from one sheet pasting them into another.

Code

Sub CountProjects()
Dim y As Long
y = 0
Sheets("Output").Activate
For Each rcell In Range("C7:W7")
    Sheets("In Flight Projects").Activate
    Range("F8").Select
    Do Until ActiveCell = ""
        If ActiveCell.Value >= rcell.Value Then y = y + 1
        ActiveCell.Offset(1).Select
    Loop
    rcell.Offset(1).Value = y
    Sheets("Output").Activate
        y = 0
Next rcell
End Sub

Display More

The code works fine, but I'm trying to make a slight change to this, but I'm unsure how to proceed.

The issue is this line of the code:

Code

For Each rcell In Range("C7:W7")

This range selects the column header row and I still like to start from column C but end when it comes to the first blank column header.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards

HaHoBe · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

Hi, ir121973,

change to read

Code

For Each rCell In Range("C7", Cells(7, Columns.Count).End(xlToLeft))

Ciao,
Holger

ir121973 · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

Hi HaHoBe, thank you very much for taking the time to rply to my post, and for the solution which works great.

All the best and kind regards

**royUK** · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

Can you attach an example workbook showing what you want to achieve, it seems really inefficient to loop through cells simply to get a count. Also, activating sheets is unnecessary

**royUK** · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

Looking at your code why even use VBA?

snb · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

I agree with RoyUK
But if you can't resist using VBA:

Code

Sub CountProjects() 
   msgbox  Sheets("In Flight Projects").Range("F8").currentregion.rows.count 
End Sub

ir121973 · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

Hi royUK, thank you for taking the time to reply to my post.

As you will see HaHoBe was able to provide the line I needed.

I too though had thought of using A formula, but this was part of a much larger script, and I'm trying to automate the process as much as possible to cater for you users who are not quite as proficient in using Excel as one would hope.

Many thanks and kind regards

ir121973 · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

Hi snb, thank you for taking the time to come back to me with this.

As you'll see HaHoBe, was able to provide the line I was looking for, and as explained to royUK, I'm trying to automate the process for the less experienced Excel user.

Many thanks and kind regards

**royUK** · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

What snb and I are pointing out is the inefficiency of your code but if you are happy that's good!

**royUK** · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

More efficient than looping

Code

Option Explicit


Sub CountProjects()
    Dim rCell As Range, rRng As Range, rRng2 As Range
    Dim y As Long


    With Sheets("In Flight Projects")
        Set rRng = .Range(.Cells(7, 3), .Cells(7, .Columns.Count).End(xlToLeft))
    End With


    With Sheets("Output")
        Set rRng2 = .Range(.Cells(8, 6), .Cells(.Rows.Count, 6).End(xlUp))
    End With


    For Each rCell In rRng
        y = Application.WorksheetFunction.CountIf(rRng2, ">" & rCell.Value)
        rCell.Offset(1).Value = y
        '        y = 0
    Next rCell


End Sub

Display More

ir121973 · Feb 20th 2014

Re: VBA Dynamic No. Of Column Headers

Hi royUK, ah I see. Yes, I'm learning, so I appreciate that my code may not be the best.

But sincere thanks for putting the code together, I really appreciate it, and certainly from a beginners perspective one I understand.

May I ask please why this method is more efficient than a loop?

Many thanks and kind regards

**royUK** · Feb 21st 2014

Re: VBA Dynamic No. Of Column Headers

A Loop does exactly that - loops through each cell. The more cells the slower the code.

My code uses a WorkSheet function to get the result which will be faster than a loop, see

Avoiding Loops

ir121973 · Feb 22nd 2014

Re: VBA Dynamic No. Of Column Headers

Hi royUK, thank you for confirming this.

All the best and kind regards

VBA Dynamic No. Of Column Headers

Participate now!

Share