后列值到A列中的最后一行，另一个值列F列A列中发布的同一行

Question

我想将值发布到列A的最后一行，然后将另一个值发布到同一行的列F那上面可能有空的空间。我将如何去完成这个？

这是我到目前为止。

Sub test411() 

    Dim iRow As Integer, iCol As Integer 
    Dim sEntity As String, sEnt2 As String, sVal1 As Integer 
    Dim wsEntry As Worksheet 
    Dim wsUp As Worksheet 
    Set wsEntry = Worksheets("Entries") 
    Set wsUp = Worksheets("Sheet1") 
    Dim lastrow As Long 

    For iRow = 6 To 7 
    lastrow = wsUp.Cells(Rows.Count, "A").End(xlUp).Row 

    wsEntry.Activate 
    iCount = WorksheetFunction.CountIf(wsEntry.Range(Cells(iRow, 4), Cells(iRow, 11)), ">0") 
    sEntity = wsEntry.Cells(5, 4).Value 
    wsUp.Range("A" & lastrow + 1, "A" & lastrow + iCount).Value = sEntity 

    For iCol = 5 To 11 
     If Cells(iRow, iCol) > "0" Then 
     sEnt2 = wsEntry.Cells(5, iCol).Value 
     sVal1 = wsEntry.Cells(iRow, iCol).Value 
     lastrow = wsUp.Cells(Rows.Count, "A").End(xlUp).Row 
     wsUp.Range("A" & lastrow + 1, "A" & lastrow + 2).Value = sEnt2 
     wsUp.Cells("I", lastrow).Value = sVal1 
     End If 

    Next iCol 

    Next iRow 

End Sub 

Answer 1

也许以下解决方案可以帮助：

Sub test411() 

Dim wsUp As Worksheet 
Dim wsEntry As Worksheet 
Dim lastrow As Long, iCount As Long 
Dim iRow As Integer, iCol As Integer 

Set wsUp = Worksheets("Sheet1") 
Set wsEntry = Worksheets("Entries") 

For iRow = 6 To 7 
    lastrow = wsUp.Cells(wsUp.Rows.Count, "A").End(xlUp).Row 
    wsEntry.Activate 
    iCount = WorksheetFunction.CountIf(wsEntry.Range(wsEntry.Cells(iRow, 4), wsEntry.Cells(iRow, 11)), ">0") 
    wsUp.Range("A" & lastrow + 1, "A" & lastrow + iCount).Value = wsEntry.Cells(5, 4).Value 
    For iCol = 5 To 11 
     If Cells(iRow, iCol) > "0" Then 
      lastrow = wsUp.Cells(wsUP.Rows.Count, "A").End(xlUp).Row 
      wsUp.Range("A" & lastrow + 1, "A" & lastrow + 2).Value = wsEntry.Cells(5, iCol).Value 
      wsUp.Cells("I", lastrow).Value = wsEntry.Cells(iRow, iCol).Value 
     End If 
    Next iCol 
Next iRow 

End Sub 

说明：而不是使用这个变量来设置另一个单元分配单元格的值到一个变量，然后，我把短路线并立即将最终单元格设置为原始单元格的值（两者之间没有变量）。像这样，您不必担心Excel VBA变量和数据类型。