如何在VBA中扩展公式超过1行代码 [英] How to extend a formula in VBA past 1 line of code

问题描述

所以我没有太多编程经验，几乎没有VBA经验。我的主要问题是我的代码中的公式超过了1行，当我包含下划线，空格然后开始换行时，我收到错误。

So I do not have too much experience with programming and almost non with VBA. My main issue is that the formula in my code extends past 1 line and when I include an underscore, space and then start a new line, I receive an error.

I附加了一段代码，可能有不必要的代码行，因为我记录了一个宏来获取代码。

I have attached a pic of the code, there is probably unnecessary lines of code because I recorded a macro to get the code.

关于我要做的事情的更多信息：

More information on what I am trying to do:

我有一个列表包含在一个单元格中使用数据验证并根据该列表中的选择，下面的单元格将输出某个列表。

I have a list contained in a cell using "data validation" and based on the selection from that list, the cell below will output a certain list.

这些列表的信息存储在工作簿中的其他工作表中。

The information for these lists are stored on other worksheets in the workbook.

我能够开发一个数据验证列表源框中的IF语句，用于多个输入。但是我有84种可能性，我无法在列表源框中填入所有单独的if语句。因此，我决定尝试使用VBA手动输入公式，方法是记录几个输入数据验证if语句的宏。

I was able to develop an IF statement in the "data validation" list source box that worked for several inputs. However I have 84 possibilities and i could not fit all the individual if statements in the list source box. Therefore I decided to try and manually input the formula using VBA by recording a macro of the several input "data validation" if statement.

这是代码：

Sub HelpSetUp()
     With Selection.Validation
         .Delete
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:= _
         "=if($B$2='fuel columns'!$A$1,agriculturalbiproduct,if($B$2='fuel      columns'!$B$1,agriculturalresidue,if($B$2='fuel columns'!$C$1,agriculturalwaste,Nofuel)))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub 

推荐答案

当你需要填写一些文本时，你需要使用&将其分解成块和_。

When you have a long bit of text to squeeze in, you need to break it up into chunks using "&" and _.

喜欢这个

dim aString as string

dim aString as string

aString = "four score and seven years ago our fathers " & _ 
    "set forth on this continent a new nation, " & _
    "conceived in liberty and dedicated to the " & _
    "proposition that all men are created equal."

请务必在&和_。

Be sure to leave a space between the & and the _.

SMW

这篇关于如何在VBA中扩展公式超过1行代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！