基于相邻小区中的标准将数据粘贴到范围中的下一个空白单元格中

1 回答

• 0

  我认为这里有一些代码可以获得范围内的下一个可用空闲单元格

  Sub capturedata()
  
  Dim sheet1, sheet2 As Worksheet
  Dim testValue As String
  Dim cashRange, rentRange As Range
  
  Set sheet1 = ActiveWorkbook.Sheets("Sheet1") ' general ledger
  Set sheet2 = ActiveWorkbook.Sheets("sheet2")
  
  testValue = sheet1.Range("F14") ' the cell with rent, cash, etc in it
  
  'the ranges
  Set cashRange = sheet2.Range("D1:D10")
  Set rentRange = sheet2.Range("D20:D30")
  
  Select Case testValue 'based on what is in "f14"
      Case "Rent"
          'paste from k14
          '****I believe the below is the part you're really concerned with***
  
          For Each cell In cashRange
              If cell.Value = "" Then 'first empty cell
                      cell.Value = sheet1.Range("k14") 'make this more dynamic with a for loop if needed
                  Exit For
              End If
          Next cell
      Case "Cash"
  
          'paste from k15
          For Each cell In rentRange ' make into a function to avoid repeated code
              If cell.Value = "" Then 'first empty cell
                      cell.Value = sheet1.Range("k14")
                  Exit For
              End If
          Next cell
  
      Case "Accounts Receivable"
         'add a for loop here  based on other criteria
      Case Else
      'case not met
  
  End Select
  
  
  End Sub
  

  回复于 2024-04-29T13:40:22+08:00