Questions about excel macros and conditions, about conditional judgments

It doesn't have to be so complicated, how can you use macros, an if and an and function is enough? Below is the formula for F3 cell.

if(and(b4>$b$1,c4>$c$1,d4<$d$1,e4>$e$1),"i standard",if(and(b4>$b$2,c4>$c$2,d4<$d$2,e4>$e$2),"ii standard","Not true"))

There is a picture and there is a truth:

Excuse me, is there anything wrong with the second data of your A material is 24 will not be "30".

dim r as integer

r = 2 'The setup starts with the second line.

do while cells(r, 11) <"" 'If the k column is not empty, execute the following statement.

if cells(r, 12) >20 and cells(r, 13) >30 and cells(r, 14) <4 and cells(r, 15) >40 then

cells(r, 16) = "o"

cells(r, 17) = "x"

elseif cells(r, 12) >20 and cells(r, 13) >20 and cells(r, 14) <4 and cells(r, 15) >50 then

cells(r, 16) = "x"

cells(r, 17) = "o"

elsecells(r, 16) = "x"

cells(r, 17) = "x"

end if 'Enter the corresponding value for the judgment condition of the if statement.

r = r + 1 'Next line.

loop 'Return to the do loop.

The if statement of the macro is different from the function, for example, the statement of the gender judgment is written like this:

if mid(x,17,1) mod 2=0 then y="Female" else y="Male"

The above is the if then else format, else can be omitted, the format is:

if ..then ..

In addition, if there are more than one statement after then and else, the format is as follows:

if ..then

elseend if

Assuming that A is listed as the name of the salesman, B is listed as a sales item, and C is listed as the sales volume, and the first row is the header, the sales volume formula for the sales item 1 of the salesman A is as follows:

sum(if((a2:a1000="Salesman A")*(b2:b1000="Sales item 1"),c2:c1000))

The formula ends with the three keys Ctrl+Shift+Enter.

The sum of the conditions judged by multiple conditions in excel does not require an array formula (i.e., ctrl + shift + enter input method).

With the sumproduct function, press enter.

sumproduct((a2:a1000="Salesman A")*(b2:b1000="Sales item 1"),c2:c1000)

The sumif function is absolutely not allowed to judge multiple conditions, only one condition can be determined.

Only the sumproduct function, or the array formula (ctrl + shift + enter input formula) can judge multiple conditions, please understand, and change the way of thinking, don't waste time to study sumif to judge multiple conditions.

If you use Excel's own formula, you can choose the following method:

sum(if('Data Sheet'e2:e51='Statistical tables'a1,if('Data Sheet'f2:f51='Statistical tables'a2,1,0)))

It should be noted that you cannot press enter directly to complete the formula, you need to hold down Ctrl and Shift and then press enter before the formula can run.

The above formula works: Find in'Data Sheet'If the median value of the column interval from E2 to E51 is equal to the content of cell A1 in the statistical table, and the content of the adjacent cell (column F) in the row of this element is equal to the content of cell A2 in the statistical table, then add up to 1, and the final formula returns the number of elements that meet the two conditions of "A1 content" and "A2 content" in the statistical table.

If you know a computer language, it's as simple as adding formulas to the macro.

There are generally two cases of multi-condition IF in VBA:

One is to use AND and OR to connect multiple conditions, for example:

if a1=1 and a2=2 or a2=""The second is to use if nesting, for example:

if a1=1 then

if a2=2 then ..else ..

elseif a2="" then ..else ..

end if

The file has been sent to you.

This app is superficially fine, and it is recommended that you set up interruptions and monitor them.

Where's your control? If it's the system that comes with it, I don't know if there's a limit in some way, if you set up a toolbar or something, I haven't studied it carefully.

If the label object is on a form, it can be called directly with the control name or the index of the control, instead of using the controls collection name.

Let me remind you of the small details, if you define the data type, is there a mistake when taking the caption of l384, it is recommended to add a conversion cint or something, and there is a merge of the control name, and turn 2+i into trim(cstr(2+i)), maybe it will become.

Then check the ON Error Resume Next statement in your VBA, please stop it in time, otherwise it will block your statement error, causing you to be unable to judge the location of the error, maybe the error is not in these sentences.