Excel 2010 VBA 引用其他工作表中的特定单元格

Excel 2010 VBA Referencing Specific Cells in other worksheets

提问by Derek

I'm sorry this is probably an extremely basic Excel VBAquestion. I've just started learning it, and I am not finding very much in the way of good tutorials. I can't find much organised information on the language at all actually.

对不起，这可能是一个非常基本的Excel VBA问题。我刚刚开始学习它，我没有找到太多好的教程。实际上，我根本找不到有关该语言的大量有组织的信息。

I've got a couple worksheets called "Sheet1" and "Sheet2".
The first and second columns of Sheet1 contain some numbers.

我有几个名为“Sheet1”和“Sheet2”的工作表。
Sheet1 的第一列和第二列包含一些数字。

I want to write a macro which prints the results of a function taking 2 variables (one each from column A and column B) to Sheet2. but I want to space these results out in the new work sheet so that the result of the function on Column 'i' in Sheet1 is put into row 1 and column 4*i. Here's what I've tried to do so far, but it hasn't worked because I don't know how to reference specific cells in other worksheets properly.

我想编写一个宏，它将一个函数的结果打印到 Sheet2，其中包含 2 个变量（A 列和 B 列各一个）。但我想在新工作表中将这些结果隔开，以便将 Sheet1 中列“i”上的函数结果放入第 1 行和第 4*i 列。到目前为止，这是我尝试做的，但没有奏效，因为我不知道如何正确引用其他工作表中的特定单元格。

Apologies that this is a very newb question, any help is much appreciated!

抱歉，这是一个非常新的问题，非常感谢您的帮助！

Sub results()

    Dim i As Integer, noValues As Integer
    noValues = Application.CountA(Range("A:A"))

    Sheets("Sheet2").Select
    Range("A1").Select
    For i = 1 To noValues
        Range("A1").Offset(0, 4 * (i - 1)).Select
        ActiveCell.FormulaR1C1 = "=Sheet1!A[i] + Sheet1!B[i]"
    Next i

End Sub

Where A[i] and B[i] should mean the value in Column A or B, row i.

其中 A[i] 和 B[i] 应表示 A 列或 B 列第 i 行中的值。

回答by Dick Kusleika

Sub Results2()

    Dim rCell As Range
    Dim shSource As Worksheet
    Dim shDest As Worksheet
    Dim lCnt As Long

    Set shSource = ThisWorkbook.Sheets("Sheet1")
    Set shDest = ThisWorkbook.Sheets("Sheet2")

    For Each rCell In shSource.Range("A1", shSource.Cells(shSource.Rows.Count, 1).End(xlUp)).Cells
        lCnt = lCnt + 1
        shDest.Range("A4").Offset(0, lCnt * 4).Formula = "=" & rCell.Address(False, False, , True) & "+" & rCell.Offset(0, 1).Address(False, False, , True)
    Next rCell

End Sub

This loops through column A of sheet1 and creates a formula in sheet2 for every cell. To find the last cell in Sheet1, I start at the bottom (shSource.Rows.Count) and .End(xlUp) to get the last cell in the column that's not blank.

这会遍历 sheet1 的 A 列，并在 sheet2 中为每个单元格创建一个公式。要查找 Sheet1 中的最后一个单元格，我从底部 (shSource.Rows.Count) 和 .End(xlUp) 开始，以获取非空白列中的最后一个单元格。

To create the elements of the formula, I use the Address property of the cell on Sheet. I'm using three of the arguments to Address. The first two are RowAbsolute and ColumnAbsolute, both set to false. I don't care about the third argument, but I set the fourth argument (External) to True so that it includes the sheet name.

为了创建公式的元素，我使用了 Sheet 上单元格的 Address 属性。我正在使用地址的三个参数。前两个是 RowAbsolute 和 ColumnAbsolute，都设置为 false。我不关心第三个参数，但我将第四个参数 (External) 设置为 True，以便它包含工作表名称。

I prefer to go from Source to Destination rather than the other way. But that's just a personal preference. If you want to work from the destination,

我更喜欢从源到目的地而不是其他方式。但这只是个人喜好。如果你想从目的地工作，

Sub Results3()

    Dim i As Long, lCnt As Long
    Dim sh As Worksheet

    lCnt = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("Sheet1").Columns(1))
    Set sh = ThisWorkbook.Sheets("Sheet2")

    Const sSOURCE As String = "Sheet1!"

    For i = 1 To lCnt
        sh.Range("A1").Offset(0, 4 * (i - 1)).Formula = "=" & sSOURCE & "A" & i & " + " & sSOURCE & "B" & i
    Next i

End Sub

回答by Jacque

I am going to give you a simplistic answer that hopefully will help you with VBA in general. The easiest way to learn how VBA works and how to reference and access elements is to record your macro then edit it in the VBA editor. This is how I learned VBA. It is based on visual basic so all the programming conventions of VB apply. Recording the macro lets you see how to access and do things.

我将给您一个简单的答案，希望对您使用 VBA 有帮助。了解 VBA 的工作原理以及如何引用和访问元素的最简单方法是录制您的宏，然后在 VBA 编辑器中对其进行编辑。这就是我学习VBA的方式。它基于 Visual Basic，因此适用于 VB 的所有编程约定。录制宏可让您了解如何访问和执行操作。

you could use something like this:

你可以使用这样的东西：

var result = 0
Sheets("Sheet1").Select
result = Range("A1").Value * Range("B1").Value
Sheets("Sheet2").Select
Range("D1").Value = result

Alternatively you can also reference a cell using Cells(1,1).ValueThis way you can set variables and increment them as you wish. I think I am just not clear on exactly what you are trying to do but i hope this helps.

或者，您也可以使用Cells(1,1).Value这种方式引用单元格，您可以设置变量并根据需要增加它们。我想我只是不清楚你到底想做什么，但我希望这会有所帮助。

回答by user3169610

Private Sub Click_Click()

 Dim vaFiles As Variant
 Dim i As Long

For j = 1 To 2
vaFiles = Application.GetOpenFilename _
     (FileFilter:="Excel Filer (*.xlsx),*.xlsx", _
     Title:="Open File(s)", MultiSelect:=True)

If Not IsArray(vaFiles) Then Exit Sub

 With Application
  .ScreenUpdating = False
  For i = 1 To UBound(vaFiles)
     Workbooks.Open vaFiles(i)
     wrkbk_name = vaFiles(i)
    Next i
  .ScreenUpdating = True
End With

If j = 1 Then
work1 = Right(wrkbk_name, Len(wrkbk_name) - InStrRev(wrkbk_name, "\"))
Else: work2 = Right(wrkbk_name, Len(wrkbk_name) - InStrRev(wrkbk_name, "\"))
End If



Next j

'Filling the values of the group name

'check = Application.WorksheetFunction.Search(Name, work1)
check = InStr(UCase("Qoute Request"), work1)

If check = 1 Then
Application.Workbooks(work1).Activate
Else
Application.Workbooks(work2).Activate
End If

ActiveWorkbook.Sheets("GI Quote Request").Select
ActiveSheet.Range("B4:C12").Copy
Application.Workbooks("Model").Activate
ActiveWorkbook.Sheets("Request").Range("K3").Select
ActiveSheet.Paste


Application.Workbooks("Model").Activate
ActiveWorkbook.Sheets("Request").Select

Range("D3").Value = Range("L3").Value
Range("D7").Value = Range("L9").Value
Range("D11").Value = Range("L7").Value

For i = 4 To 5

If i = 5 Then
GoTo NextIteration
End If

If Left(ActiveSheet.Range("B" & i).Value, Len(ActiveSheet.Range("B" & i).Value) - 1) = Range("K" & i).Value Then
    ActiveSheet.Range("D" & i).Value = Range("L" & i).Value
 End If

NextIteration:
Next i

'eligibles part
Count = Range("D11").Value
For i = 27 To Count + 24
Range("C" & i).EntireRow.Offset(1, 0).Insert
Next i

check = Left(work1, InStrRev(work1, ".") - 1)

'check = InStr("Census", work1)
If check = "Census" Then
workbk = work1
Application.Workbooks(work1).Activate
Else
Application.Workbooks(work2).Activate
workbk = work2
End If

'DOB
ActiveWorkbook.Sheets("Sheet1").Select
ActiveSheet.Range("D2").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.Workbooks("Model").Activate
ActiveWorkbook.Sheets("Request").Select
ActiveSheet.Range("C27").Select
ActiveSheet.Paste

'Gender
Application.Workbooks(workbk).Activate
ActiveWorkbook.Sheets("Sheet1").Select
ActiveSheet.Range("C2").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.Workbooks("Model").Activate
ActiveWorkbook.Sheets("Request").Select
'Application.CutCopyMode = False

ActiveSheet.Range("k27").Select
ActiveSheet.Paste

For i = 27 To Count + 27
ActiveSheet.Range("E" & i).Value = Left(ActiveSheet.Range("k" & i).Value, 1)
Next i

'Salary
Application.Workbooks(workbk).Activate
ActiveWorkbook.Sheets("Sheet1").Select
ActiveSheet.Range("N2").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.Workbooks("Model").Activate
ActiveWorkbook.Sheets("Request").Select
'Application.CutCopyMode = False

ActiveSheet.Range("F27").Select
ActiveSheet.Paste


ActiveSheet.Range("K3:L" & Count).Select
selction.ClearContents
End Sub