Store location of cell and loop

諏嵍股嘲

Option Explicit

Sub setDateValues()

    Dim ws2 As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim beginnerCell As Range
    Dim nextEmptyDateCell As Range
   
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
   
   'set range for column B
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("B2:B" & _
                     ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row)
                                                  
                                                  
   'set initial cell for beginnerCell / alternatively
    'simply “A6”? I think this variable might only be needed
    'for the first loop, or maybe I can re-set it to the last cell in the loop?
    Set beginnerCell = ws2.Cells(Rows.Count, "A").End(xlUp).Offset(5, 0)
   

   'loop through column B range; find next non-empty cell and attribute cell value
   'to beginnerCell in Sheet2
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            beginnerCell.Value = cell.Value
           cell = beginnerCell
            Exit For
        End If
    Next cell

    Dim LastRow As Long
   
    'find last used row within B:C range
    LastRow = WorksheetFunction.Max(ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row, _
                                    ws2.Cells(ws2.Rows.Count, "C").End(xlUp).Row)

   're-set beginnerCell  as two cells below that row, in column 1
    Set beginnerCell = ws2.Cells(LastRow + 2, "A")
   

      
    Dim nextCellStoreA As Range
    Dim nextCellStoreB As Range


   'set range for column A to loop through
    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A" & _
    ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
   
    'set initial cell for nextCellStoreA
    Set nextCellStoreA = ws2.Cells(Rows.Count, "B").End(xlUp).Offset(5, 0)
   
    'set initial cell for nextCellStoreB
    Set nextCellStoreB = ws2.Cells(Rows.Count, "C").End(xlUp).Offset(5, 0)
   
    'same issue as in the first loop; the loop starts from the beginning again,
    'maybe I could create a variable that stores the address of the last cell?
   
    'loop through Sheet1!ColumnA range and assign values in Sheet2
    For Each cell In rng
        If cell.Value = "StoreB" Then
            nextCellStoreB.Value = cell.Offset(, 2).Value
            Set nextCellStoreB = nextCellStoreB.Offset(1, 0)

            ElseIf cell.Value = "StoreA" Then
            nextCellStoreA.Value = cell.Offset(, 2).Value
            Set nextCellStoreA = nextCellStoreA.Offset(1, 0)

        End If
                    If cell.Value = "." Then Exit For
    Next cell

End Sub

ykcbf1100

应该是从哪里转来的代码，全英文，也没看明白。没附件，也没办法调试。

taller

https://stackoverflow.com/questi ... y/78532874#78532874