如何将整数变量传递给vba公式 [英] How to pass an integer variable into a vba formula

问题描述

  Sub lol（）
我有一些麻烦，我写的这段代码： 
 Dim counter As Integer 
 
 counter = 1 
 
 Do Until Selection.Value =
 Dim ws As Worksheet 
 Sheets（ Row1）。复制后：= ThisWorkbook.Sheets（ThisWorkbook.Sheets.Count）
设置ws = ActiveSheet 
 
 
 
表格（Sheet1）。 
 ws.Name =Row+ CStr（ActiveCell.Value）
设置newSelection = ActiveCell.Offset（1，0）
 
 ws.Select 
 ActiveCell。公式R1C1 == Sheet1！R [计数器] C [1]
 ws.Range（A1）选择
 Selection.AutoFill目的地：= ws.Range（A1：C1选择
表格（Sheet1）选择
 newSelection.Select 
 
 counter = counter $ 1 
 
循环
 
 ActiveCell.Offset（-1，0）。选择
 
 MsgBox（所有表更新）
 
 
 End Sub 
  

I运行此行时出错：

  ActiveCell.FormulaR1C1 == Sheet1！R [counter] C [1]
  

但我不知道为什么。我将该变量定义为整数，所以我不明白为什么它不起作用。如果我删除计数器，而只是写= Sheet1！R [1] C [1]它工作正常，所以我知道那里的东西必须是一个整数。 （特别是问题是它将始终是同一行，但是我需要它为每次迭代删除一行，这就是为什么我创建了计数器变量，但是并没有真正了解为什么它不起作用。任何想法（是我已经定义了变量类型不正确，或者这是不是正确的语义上做我想做的事情？）

解决方案

对counter的引用需要在引号之外：

  ActiveCell.FormulaR1C1 = = Sheet1！R [& counter&] C [1]
  

I am having a bit of trouble with this code that I have written:

Sub lol()

Dim counter As Integer

counter = 1

Do Until Selection.Value = ""
    Dim ws As Worksheet
    Sheets("Row1").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Set ws = ActiveSheet



    Sheets("Sheet1").Select
    ws.Name = "Row " + CStr(ActiveCell.Value)
    Set newSelection = ActiveCell.Offset(1, 0)

    ws.Select
    ActiveCell.FormulaR1C1 = "=Sheet1!R[counter]C[1]"
    ws.Range("A1").Select
    Selection.AutoFill Destination:=ws.Range("A1:C1"), Type:=xlFillDefault
    ws.Range("A1:C1").Select
    Sheets("Sheet1").Select
    newSelection.Select

    counter = counter + 1

    Loop

    ActiveCell.Offset(-1, 0).Select

    MsgBox ("All sheets updated")


End Sub

I get an error when running this line:

ActiveCell.FormulaR1C1 = "=Sheet1!R[counter]C[1]"

But I am not sure why. I defined that variable as an integer and so I do not understand why it won't work. If I remove the counter, and instead just write "=Sheet1!R[1]C[1]" it works fine, so I know the thing in there has to be an integer. (Problem with that in particular is that it will always be the same row, but I need it to drop one row for each iteration, which is why I created the counter variable, but don't really get why it won't work. Any thoughts? (Is it that I have defined the variable type incorrectly, or is this just not the right semantically to do what I am trying to do?)

解决方案

The reference to 'counter' needs to be outside the quotations:

ActiveCell.FormulaR1C1 = "=Sheet1!R[" & counter & "]C[1]"

这篇关于如何将整数变量传递给vba公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！