vba - 循环遍历不同工作表中的范围 (Excel)

Question

我有两张表:“Project_Name”和“Admin”。

我还有一组关于两者的数据。我有一个代码尝试循环遍历两张表中的数据，但它不起作用。

如果循环仅在一张纸上完成，而不是在两张纸上完成，则代码有效。我不确定问题是什么？

Dim val1 As String
Dim val2 As String
Dim val3 As String

For i = 1 to LastRow

With Worksheets("Project_Name")
  .Range(i, 1).Value = val1
  .Range(i, 2).Value = val2
End With

With Worksheets("Admin")
  .Range(i, 1).Value = val3
End With

Next i

Answer 1

尝试这个并调整变量，这对我有用(注意 .Range 被 .Cells 取代):

Sub test2()

' Active workbook
Dim wb As Workbook
Set wb = ThisWorkbook
Dim i As Integer

'*******************************************
'Adapt this vars


'define your sheets
Dim ws_pname As Worksheet
Dim ws_admin As Worksheet
Set ws_pname = wb.Sheets("Project_Name")
Set ws_admin = wb.Sheets("Admin")

'define your values
Dim val1 As String
Dim val2 As String
Dim val3 As String

val1 = "test_val1"
val2 = "test_val2"
val3 = "test_val3"

'definie the last Rows
Dim lastRow_pname As Integer
Dim lastRow_admin As Integer

lastRow_pname = ws_pname.Range("A" & Rows.Count).End(xlUp).Row + 1
lastRow_admin = ws_admin.Range("A" & Rows.Count).End(xlUp).Row + 1
'*******************************************

For i = 1 To lastRow_pname


With ws_pname
  .Cells(i, 1).Value = val1
  .Cells(i, 2).Value = val2
End With

Next i

For i = 1 To lastRow_admin
With ws_admin
  .Cells(i, 1).Value = val3
End With
  Next i

End Sub

编辑:下面的代码只有一个循环。

Sub test2()

' Active workbook
Dim wb As Workbook
Set wb = ThisWorkbook
Dim i As Integer

'*******************************************
'Adapt this vars


'define your sheets
Dim ws_pname As Worksheet
Dim ws_admin As Worksheet
Set ws_pname = wb.Sheets("Project_Name")
Set ws_admin = wb.Sheets("Admin")

'define your values
Dim val1 As String
Dim val2 As String
Dim val3 As String

val1 = "test_val1"
val2 = "test_val2"
val3 = "test_val3"

'definie the last Rows
Dim lastRow_pname As Long
Dim lastRow_admin As Long
Dim lastRow As Long

lastRow_pname = ws_pname.Range("A" & Rows.Count).End(xlUp).Row + 1
lastRow_admin = ws_admin.Range("A" & Rows.Count).End(xlUp).Row + 1
'*******************************************

'determine biggest last row
If lastRow_pname >= lastRow_admin Then
    lastRow = lastRow_pname
Else
    lastRow = lastRow_admin
End If


For i = 1 To lastRow

    If i <= lastRow_pname Then
      With ws_pname
        .Cells(i, 1).Value = val1
        .Cells(i, 2).Value = val2
      End With
    End If

    If i <= lastRow_admin Then
      With ws_admin
        .Cells(i, 1).Value = val3
      End With
    End If

Next i

End Sub