VBA help - summing a certain text-value in varying ranges

Hi

I new to this forum as well as to VBA programming in general.

I'm studying sociology, and working with GDA (geometric data analysis) I often have to do some standard calculation in the result-sheet (Excel) produced by the data mining program (SPAD).

I'm quite experienced in using the regular excel functions, but in trying to make a standard sheet pre-figured to do the calculation some troubles occur which I can't get my head around using regular excel functions.

That's why I think I now have to take it a step further learning to do macros and VBA stuff.

Here's what I need to do (it must be possible??? some how)

In the calculation I need know the number of variables (questions) in my analysis, that does not contain passive modalities. And no, the program doesn't give me that information by it self. I can easily count it manually every time, but yeah...

The first sheet in the result-excel-file contains the frequency-tables of the variables/questions, and the set-up of this page varies from time to time due to different number of questions, and the number of modalities in each question.

But in column D I'll always find a cell with the text-value "Count after cleaning" in the first row of a question.. Continuing down column D the absolute frequency of the modalities to the question occurs. When a modality is passive (and that's what I want to count) the cell (text)-value will be "Illustrative". If each question could have one and only one illustrative modality it would be an easy task - but that's not the case..

I therefore need a macro telling me how many times 1 or more text-values="illustrative" occur between two cells containing the value "Count after cleaning" in column D.

Example:

D
1..... Count after cleaning
2..... 2
3..... 4
4..... Illustrative
5..... Illustrative
6..... 3
7..... [Empty]
8..... Count after cleaning
9..... 9
10... Illustrative
11... 4
12... [Empty]
13... Count after cleaning
14... 2
15... 1
16... 6
17... [Empty]

and so on..... The number of questions and modalities to each question varying, making the range "float" from time to time.

Running the macro on this sheet should tell me either:
1) You have 3 Questions/Variables of which 2 contains passive modalities, or
2) You have 3 Questions/Variables of which 1 contaions only active modalities, i.e. no passive modalities.

Is that possible??


Jacob

It's not clear if you can add additional calcs or not... a basic formula in E would allow you to generate your results quite efficiently

Please Login or Register  to view this content.

Your result is then:

Please Login or Register  to view this content.

thanks... It think I almost there know.

I can add additional calcs in the result-sheets.. My idea though was to make a standard sheet with all the formulas in it, in which I can then copy-past the results every time I run an analysis, and thereby doing all the calcs automatically.

The codes you wrote me seems to be going in the right direction (maybe the fault is on me, I have to translate them - damn you danish excel version ), but:

It returns the value #NAME? in column E (or G, where I put it, as column G is occupied), every time the "illustrative" occurs. Maybe the "#NAME?" thing is caused by my translation, but what causes the fact, that it counts it twice?

Ideally I should get a "1" value in column G in the ..Question headings.. row (with the "count after cleaning"-value.

Something like:
If "count after cleaning" in D1 return "1" in G1 if count of "illustrative">0 before the next "count after cleaning"-value in D1..
does that make any sense?

D [...] G
0 count after cleaning ....... 1
1 1................................... 0
2 3................................... 0
3 5 ...................................0
4 illustrative ......................0
5 4 ...................................0
6 22..................................0
7 illustrative....................... 0
8 [ ] ..........................0
9 count after cleaning ........0
10 2................................... 0
11 5 ...................................0
12 89 ..................................0
13 0 ................................... 0
14 43.................................. 0
15 243 ................................0
16 [ ]............................0
17 count after cleaning ..........1
18 2 ................................... 0
19 5 ................................... 0
20 46................................... 0
21 2.....................................0
22 illustrative ........................0
23 1.................................... 0
24 43................................... 0
25 [ ]........................... 0
....
50 count after cleaning .......... 0
51 124.................................. 0
52 3 ....................................0
53 5.................................... 0
54 35................................... 0
55 2 .................................... 0
.......

The zeroes returned in column G could also just be empty cells.

The result code work just fine, btw.

Though I could give the translated versions I think it is simplest to provide you with a working example which will auto translate when opened (text terms excepted)

From this you should be able to adapt a version in your real file.

(I've assumed you have no header row - if you do then you wouldn't need the INDEX within the LOOKUP)

edit: though the below will work I believe the original works also - please post a sample with original formula in place demonstrating the error (assumes you corrected all references to row 1 to row 4)


My fault - I edited my original reply to something that didn't work

Using the latest sample - given you do have header rows and accounting for my error - try:

Please Login or Register  to view this content.

Originally Posted by DonkeyOte

please post a sample with original formula in place demonstrating the error (assumes you corrected all references to row 1 to row 4)

Here's how it looks now.

It seems my translation of LOOKUP was not correct... hmm.

I think we're going around in circles a little - as I see it both suggestions should work - attached is a version with both in place (which should translate)