当将字符串写入单元格时，VBA会插入额外的单引号 [英] When writing string to a cell, VBA inserts extra single quotation marks

问题描述

  ActiveCell.Value == f（R [-1] C [0]，Sheet1！& ColumnLetter& i&）
  

其中ColumnLetter是我的宏计算较早的一些变量字母，f是一些函数，我是一些数字。

问题是当我运行这个单元格时这取代：（如果ColumnLetter = F，i = 16）：

  = f（R [-1] C [0] ，Sheet1！'F16'）
  

但我想要：

  = f（R [-1] C [0]，Sheet1！F16）
  

为什么VBA或Excel将这些单引号放在F16周围？如果在我的公式中不包含R [-1] [0]作为参数，它不会插入这些额外的引号，但是我需要包含这个。

帮助非常感谢！

解决方案

它的组合 R1C1 和 A1 寻址。您需要选择一种方法并将其用于两个部分。

请注意，如果键入 = f（R [-1] C [0]，Sheet1！F16）进入一个单元格，你会得到一个错误，因为相同的原因。

你说你需要使用R1C1风格的第一个地址，但（假设这个是因为你不想要
的绝对地址）你可以使用 .Offset 而不是

  ActiveCell.Value == f（& Replace（ActiveCell.Offset（-1，0）.Address，$，）_ 
&，Sheet1！ & ColumnLetter& i&）
  

I am trying to write this formula using VBA:

ActiveCell.Value = "=f(R[-1]C[0],Sheet1!" & ColumnLetter & i & ")"

Where ColumnLetter is some variable letter which my macro computes earlier, and f is some function, and i is some number.

The problem is that when I run this, the cell is given this instead: (if ColumnLetter = F, i = 16):

=f(R[-1]C[0],Sheet1!'F16')

but I want:

=f(R[-1]C[0],Sheet1!F16)

Why is VBA or Excel putting those single quotation marks around F16? It does not insert these extra quotation marks if I do not include R[-1][0] as an argument in my formula, but I need to include this.

Help much appreciated!

解决方案

Its the combination of R1C1 and A1 addressing. You need to pick one method and use it for both parts.
Note that if you type =f(R[-1]C[0],Sheet1!F16) into a cell you will get an error for the same reason.

You say you need to use R1C1 style for the first address, but (assuming this is because you don't want absolute address) you can use .Offset instead

ActiveCell.Value = "=f(" & Replace(ActiveCell.Offset(-1, 0).Address, "$", "") _
 & ",Sheet1!" & ColumnLetter & i & ")"

这篇关于当将字符串写入单元格时，VBA会插入额外的单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！