[RESOLVED] Counting non-blank cells in constantly changing range

I've been using the following code to count the number of employees that participated in a particular job task:

NumOfWorkers = Application.CountA(Sheets("Paycard").Range("F3:K3")

I need to know if it's possible with this function to now set the "Range" portion of it to a constantly changing set of variables? What is the syntax?

For example, as I adjust the variable 'currentrow' plus one after each loop, I need to know the NEW number of workers for that particular row, not just row 3.

Make sense? Help?

try like

vb Code:

NumOfWorkers = Application.CountA(Sheets("Paycard").Range("F3:K" & rw)

where rw is your row variable

Quote:

---

Originally Posted by westconn1

try like

vb Code:

NumOfWorkers = Application.CountA(Sheets("Paycard").Range("F3:K" & rw)

where rw is your row variable

---

Definitely appreciate the input, but that route doesn't get the job done.

.Range("F" & rw:"K" & rw) returns a compile error.

As does a whole set of combinations like:

.Range(""F" & rw: "K" & rw"")


I need to be able to change the row variable for both sides of the range.

there are anumber of ways to attack this problem, but they all need a little more information than you have given...

for a start why do you curtail the row to just this range what is outwith the range?

why are you looping through the rows or have i missed somethig here?

i think i got the idea that you want to do a block from f to k fro row 1 to 6 for example?

here to help

Quote:

---

Originally Posted by incidentals

there are anumber of ways to attack this problem, but they all need a little more information than you have given...

for a start why do you curtail the row to just this range what is outwith the range?

why are you looping through the rows or have i missed somethig here?

i think i got the idea that you want to do a block from f to k fro row 1 to 6 for example?

here to help

---

You got it. I have a worksheet where the crew leader for the day adds the list of jobs that crew did, one to each row, then lists the crew members that participated in that particular job horizontally, starting in column F and moving right. There could be anywhere from 1 to 8 or 9 crew members on a particular job.

Instead of having the crew leader type in manually how many people were on the jobsite, THEN list them all out, I simply wanted to calculate how many people were on a particular jobsite based on how many names were entered in from Column F on through Column K (for the current row) or however many I need to contain all the names.

After calculating pay for the current row, I add one to the row counter and loop through again, adding up each crew member's pay for the whole week for all jobs put together.

Our crew members are paid based on a percentage of the overall job amount; that percentage changes depending on how many people were working on the job.

You were on the right track, yes.

the loop man not be necessary!
there are many ways to skin this particular cat!
what are the the preceeding columns a to e?

i ask because you may be able to filter the row in some way to get a range-less answer back...

here to talk

just so you kow its worthwhile talking ...

suggestion 1 ( programming aside ) a new column e is introduced and returns the countif() of the rest of the row.
suggestion 2 ( programming aside ) the rest of the row is a named range - this is because names ranges expand when new columns are inserted - requires that the users insert new columns rather than just put more data in the next available column so its prone to disaster, but is used in some situations!

just the first 2 thoughts

Quote:

---

Originally Posted by incidentals

suggestion 1 ( programming aside ) a new column e is introduced and returns the countif() of the rest of the row.

---

This is the route I ended up taking. It's not as technically clean as I usually like to do things, but I simply picked the next unused column, filled it down with a "countA" formula, then set the text in that column to invisible and locked it for editing.

The macro knows it's there and uses the employee count from that column, but the end user (i.e. my employees) don't and never see it.

I'm convinced (without any real justification) that there is a way to do this programming-wise, but hell....if it works, it works.

I'll sleep at night, even though I know it's there.

Thanks for the suggestions.

please rate the post if it helped and close the thread using the toolbar at the top

Quote:

---

As does a whole set of combinations like:

.Range(""F" & rw: "K" & rw"")

---

try

vb Code:

.range("f" & rw & ":K" & rw)

or use cells(rw, col) for start & end of range