Excel VBA：无法将我的功能粘贴到单元格中 [英] Excel VBA: can't paste my function into a cell

问题描述

我正在尝试粘贴

  = IF（AND（C4-C3 <（$ I $ 1/1000） A4 = A3），OOOO，-----）
  

我通过

  Range（I3）选择
 ActiveCell.FormulaR1C1 == IF（AND（ （34）&，& Chr（34） - （$ I / ----& Chr（34）&）
 Range（I3）。选择
  

返回运行时错误'1004'
应用程序定义或对象定义错误

我已经尝试过双引号，看到&安培; Chr（34）&解决方法，也试过。没有什么工作。
另外，像

  ActiveCell.FormulaR1C1 == IF（
  

不工作，但

  ActiveCell.FormulaR1C1 == IF
  

。

奇怪的是，这在代码

  Range（C3）中选择
 ActiveCell .FormulaR1C1 == MID（B3,1,2）* 3600 + MID（B3,4,2）* 60 + MID（B3,7,9）
范围（C3）。选择
 $ b如果我粘贴= MID（B3,1,2）* 
 $ / code $ c 
 
 3600 + MID（B3,4,2）* 60 + MID（B3,7,9）（而不是我试图使用的字符串），在调试时进入问题代码，然后点击Continue，它将粘贴该行，所以它没有发生在两个语句之间发生什么事情之间只有一些选择和复制/粘贴，但如果你想看
 
 
，我会把它包括在这里选择
 ActiveCell.FormulaR1C1 == MID（B3,1,2）* 3600 + MID（B3,4,2）
  * 60 + MID（B3,7,9）
范围（C3）。选择
 Selection.Copy 
范围（D3）。选择
 Selection.End （xlDown）。选择
 ActiveSheet.Paste 
范围（K3：Z4）。选择
 Application.CutCopyMode = False 
 Selection.Copy 
范围（K1 ）。选择
 ActiveSheet.Paste 
列（H：H）。选择
 Application.CutCopyMode = False 
 Selection.Cut 
列（M：选择
 ActiveSheet.Paste 
列（I：I）。选择
 Selection.Cut 
列（H：H）。选择
 ActiveSheet.Paste 
范围（I3）。选择
 ActiveCell.FormulaR1C1 == IF（AND（C4-C3（$ I $ 1/1000），A4 = A3），& Chr（34）& OOOO& Chr（34）& ，& Chr（34）& -----& Chr（34）& ）
 Range（I3）。选择
  
 
 
解决方案
 快速回答 
 
 
 
您需要将公式更改为R1C1样式 - 甚至更简单，将公式分配给 .Formula 而不是 .FormulaR1C1 属性。
 
 
 
 更长的解释： 
 
 
 
 Excel有两种引用单元格的方式：
 
 
默认的 A1 符号，您可以通过提供列字母和行号引用单元格，并将其添加到行或列前面，前缀为 $ 。
 
 
通过使用 R指定行来引用单元格的 R1C1 符号和行号，列为 C 和列号。如果数字在 [] 括号中提供，则引用是相对的，不带括号，它是绝对的。要使用此符号，您需要将其设置为Excel选项（在公式下）
 
 
 R1C1 符号是在应用于多个单元格时相对引用不更改！例如，C1中输入的A1公式 = A1 + B1 将是 = RC [-2] + RC [-1] 。如果现在将公式复制到C列，行号将在 A1 样式的每一行中更改（例如 = A2 + B2 ， = A3 + B3 等）。在R1C1 符号中，它是完全相同的公式，没有任何改变。
 
 
 
在正常的Excel UI中，这并不重要，只是一个偏好的问题，因为只要您使用 $ 。* 
 $ b $正确修改绝对参考，Excel将自动处理调整公式。 b 
 
但是，它在宏中变得更加有用，如果要搜索和替换引用！想象一下，在上面的例子中，您想将公式从 = A1 + B1 更改为 = A1 + A2   - 无处不在该公式被使用。在 A1 中，您不能简单地用A2搜索并替换B1，因为B1将变为B2，B3等等。但是，在R1C1 中，您只需将 RC [-1] 与 R [1] C 你完成了。
 
 
 
因此，Excel宏记录器还记录了R1C1中的公式  - 它是一样的公式，无论你在哪里申请！但是，每个范围有一个 .Formula 和一个 FormulaR1C1 属性，您可以设置 - 另一个属性将相应更新。因此，使用适合您需要的任何款式。
 
 
 
在您的情况下，如果您计划使用R1C1 它还与其他单元格比I3。
 
 
 
  A1 ：
 Range（I3）。Formula == IF （AND（C4-C3 <（$ I $ 1/1000），A4 = A3），OOOO，-----）
 Range（J4 = IF（AND（D5-D4≤（$ I $ 1/1000），B5 = B4）， OOOO   ----- ）
 
 
 
 
  R1C1 ：
 strFormula == IF（AND（R [1] C [-6] -RC [-6] <（R1C9 / 1000），R [1] C [-8] = RC [-8]），OOOO，-----）
范围）.FormulaR1C1 = strFormula 
 Range（J4）。FormulaR1C1 = strFormula 
 
 
 
 
 HTH！
 
 
 
（*有趣的侧节点：在第一个版本的Excel中，Microsoft将 R1C1 用作默认符号，因为它优于 A1 但是，由于Lotus 1-2-3是当时的标准，而已经被接受为标准，所以它在以后的版本中恢复了这种符号。）
 
I'm trying to paste 
=IF(AND(C4-C3<($I$1/1000),A4=A3),"OOOO","-----")
into a cell, which I do via
 Range("I3").Select
 ActiveCell.FormulaR1C1 = "=IF(AND(C4-C3<($I$1/1000),A4=A3)," & Chr(34) & "OOOO" & Chr(34) & "," & Chr(34) & "-----" & Chr(34) & ")"
 Range("I3").Select
That returns Run time error '1004'
Application-defined or object-defined error

I already tried double quotes and saw the & Chr(34) & workaround and tried that too. Nothing works.
Furthermore, something as simple as     
 ActiveCell.FormulaR1C1 = "=IF("
doesn't work, but 
 ActiveCell.FormulaR1C1 = "=IF" 
does.

Strangely, this works in the code
Range("C3").Select
ActiveCell.FormulaR1C1 = "=MID(B3,1,2)*3600+MID(B3,4,2)*60+MID(B3,7,9)"
Range("C3").Select
which I do earlier.
If I paste "=MID(B3,1,2)*3600+MID(B3,4,2)*60+MID(B3,7,9)" (instead of the string I'm trying to use), into the problem code while debugging and hit Continue, it will paste that line, so its not anything that happens in between the two statements. What happens between there is just some selection and copy/pasting but I'll include it here anyway if you want to look
    Range("C3").Select
  ActiveCell.FormulaR1C1 = "=MID(B3,1,2)*3600+MID(B3,4,2)*60+MID(B3,7,9)"
Range("C3").Select
Selection.Copy
Range("D3").Select
Selection.End(xlDown).Select
ActiveSheet.Paste
Range("K3:Z4").Select
Application.CutCopyMode = False
Selection.Copy
Range("K1").Select
ActiveSheet.Paste
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Cut
Columns("M:M").Select
ActiveSheet.Paste
Columns("I:I").Select
Selection.Cut
Columns("H:H").Select
ActiveSheet.Paste
Range("I3").Select
ActiveCell.FormulaR1C1 = "=IF(AND(C4-C3<($I$1/1000),A4=A3)," & Chr(34) & "OOOO" & Chr(34) & "," & Chr(34) & "-----" & Chr(34) & ")"
Range("I3").Select

解决方案
Quick answer:

You need to change either your formula to R1C1 style - or even simpler, assigned the formula to the .Formula instead of .FormulaR1C1 property.

Longer explanation:

Excel has two ways of referencing cells: 

The default A1 notation, where you refer to a cell by providing the column letter and the row number - and fix it to either row or column by prefixing a $.
The R1C1 notation where you refer to a cell by specifying it row with R and the row number and the column with Cand the column number. If the number is provided in [] brackets, the reference is relative, without brackets it absolute. To use this notation, you need to set it in the Excel options (under Formulas)
The advantage of the R1C1 notation is that relative references do not change when applied to multiple cells! For example the A1 formula =A1+B1 entered in C1 would be =RC[-2]+RC[-1]. If the formula is now copied down column C, the row number will change in each row in A1 style (e.g. =A2+B2, =A3+B3, etc.). In R1C1 notation, it is exactly the same formula without any change.

In the normal Excel UI, this doesn't matter too much and is simply a matter of preference, as Excel automatically takes care of adjusting the formulas as long as you fix the absolute reference properly with the $.*

However, it becomes much more useful in macros and if you want to search&replace references! Imagine in the above example, you want to change the formula from =A1+B1 to =A1+A2 - everywhere that this formula is used. In A1, you cannot simply search&replace B1 with A2, as B1 will become B2, B3, and so forth. However, in R1C1, you simply reaplce RC[-1] with R[1]C and you're done.

For that reason, the Excel Macro Recorder also records the formulas in R1C1 - it is simply the same formula, no matter where you apply it to! However, every Range has a .Formula and a FormulaR1C1 property that you can set - and the other property will be updated accordingly. Therefore, use whatever style is suited be for your need.

In your case, the R1C1 formula would make sense, if you plan to use it also with other cells than I3.

A1:
Range("I3").Formula = "=IF(AND(C4-C3<($I$1/1000),A4=A3),""OOOO"",""-----"")"
Range("J4").Formula = "=IF(AND(D5-D4<($I$1/1000),B5=B4),""OOOO"",""-----"")"


R1C1:
strFormula = "=IF(AND(R[1]C[-6]-RC[-6]<(R1C9/1000),R[1]C[-8]=RC[-8]),""OOOO"",""-----"")"
Range("I3").FormulaR1C1 = strFormula
Range("J4").FormulaR1C1 = strFormula


HTH!

( * Interesting side node: in the first version of Excel, Microsoft used the R1C1 as the default notation, as it is superior to A1 in multiple ways. However, as Lotus 1-2-3 was the standard by then and A1 already accepted as the norm, it reverted this notation in later versions.)

                        
这篇关于Excel VBA：无法将我的功能粘贴到单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！