excel - VBA中的特殊IF / THEN语句和数据验证列表(Special IF/THEN Statement And Data Validation List In VBA)

Question

The best way I can explain what I need is this:

(我能解释我需要的最好方法是：)

• If A1 = "" and A3 = "" , then put a data validation list in cell A2.

  (如果A1 = ""和A3 = "" ，则将数据验证列表放在单元格A2中。)

• If A1 = "" and A3 <> "" , then A2 = A3 .

  (如果A1 = ""和A3 <> "" ，则A2 = A3 。)

• If A1 <> "" and A3 = "" , then A2 = A1

  (如果A1 <> ""且A3 = "" ，则A2 = A1)

• If A1 <> "" and A3 <> "" , then A2 = A1

  (如果A1 <> ""和A3 <> "" ，则A2 = A1)

I know I'm leaving out a lot of information, but if anyone could write this in VBA Code where it automatically updates when A1 or A3 are changes, I would appreciate it.

(我知道我遗漏了很多信息，但是如果有人可以用VBA代码编写此信息，并在A1或A3更改时自动更新，我将不胜感激。)

I already have a named range that I want to use for the Data Validation List.

(我已经有一个要用于数据验证列表的命名范围。)

I am working in Excel 2016, but I need this to work with Excel 2010 as well.

(我正在使用Excel 2016，但也需要将其与Excel 2010一起使用。)

I know, quite literally, nothing when it comes to coding.

(从字面上看，我不知道编码。)

Edit:

(编辑：)

If it were possible to write this in a formula in cell A2, it would go as follows:

(如果可以在单元格A2中的公式中编写此代码，则它将如下所示：)

=if(and(A1="",A3=""),Named_Range,if(and(A1="",A3<>""),A3,if(and(A1<>"",A3=""),,A1,if(and(A1<>"",A3<>""),A1,""))))

Whereas "Named_Range" would make the cell a data validation list with the named range "Example 1" .

(而“ Named_Range”将使该单元格成为数据验证列表，其命名范围为"Example 1" 。)

In every other instance, the data validation list would be deleted and would equal the respective cell.

(在每个其他实例中，数据验证列表将被删除，并且将等于相应的单元格。)

However, I know that this is not possible (I think) without using Worksheet Change in VBA Code.

(但是，我知道，如果不使用VBA代码中的“工作表更改”，这是不可能的（我认为）。)

I am not familiar with writing VBA code, but I do know you can write If/Then statements and Data Validation Lists in VBA code.

(我对编写VBA代码不熟悉，但是我知道您可以在VBA代码中编写If / Then语句和数据验证列表。)

I just need to make that "Formula" in VBA Code in excel.

(我只需要在Excel中的VBA代码中创建“公式”即可。)

  ask by srcarter2012 translate from so

Answer 1

For a non-coding solution you could add two additional rows (and swap row 2 & 3) to allow for the automation:

(对于非编码解决方案，您可以添加另外两行（并交换行2和3）以实现自动化：)

Row 1: Values

(第1行：值)

Row 2: Values

(第2行：值)

Row 3: =if(and(A1="",A2=""),"SELECT FROM DROPDOWN BELOW",if(and(A1="",A2<>""),A2,if(and(A1<>"",A2=""),,A1,if(and(A1<>"",A2<>""),A1,""))))

(第3行： =if(and(A1="",A2=""),"SELECT FROM DROPDOWN BELOW",if(and(A1="",A2<>""),A2,if(and(A1<>"",A2=""),,A1,if(and(A1<>"",A2<>""),A1,"")))))

Row 4: Dropdown list where the first option is blank ""

(第4行：下拉列表，其中第一个选项为空白“”)

Row 5: =IF(A3<>"SELECT FROM DROPDOWN BELOW", A3, IF(A4<>"",A4,"SELECTION NEEDED"))

(第5行： =IF(A3<>"SELECT FROM DROPDOWN BELOW", A3, IF(A4<>"",A4,"SELECTION NEEDED")))

Much taller, but fits the given requirements.

(高得多，但符合给定的要求。)