使用VBA如果then语句复制和粘贴数据

2 回答

• 1

  您可能希望考虑以下代码与代码或Rodger相同但比使用 Select 和复制/粘贴语法更快，而不是使用大量内存并使程序运行缓慢的复制/粘贴 .

  Sub Test()
  Dim nRows As Long, LastRow As Long   'Declare as Long instead of Integer to avoid overflow
  Dim CurYear As Date, StartDate As Date
  
  LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Count the last used row in column 1 where you put the first data (dates)
  nRows = 2    'Set the starting point of row where you put the first data (dates). In this example I use 2
  CurYear = Range("I265").Value
  StartDate = Range("M5").Value
  
  Do
      If Cells(nRows, 1).Value < CurYear And Cells(nRows, 1) > StartDate Then 'Use And not &
          Cells(nRows, 5).Value = Cells(nRows, 2).Value   'This is essentially a "copy/ paste" syntax. Change the value (5) to the column you want to paste the value in column 2
      End If
      nRows = nRows + 1   'Set an increment value so each looping the nRows will increase by 1
  Loop Until nRows = LastRow + 1   'Added by 1 so that the data in LastRow will keep being processed
  
  End Sub
  

  回复于 2024-04-28T01:32:54+08:00
• 0

  将“option explicit”放在代码的顶部（在sub之前），它会告诉你要解决的问题 . 这样做会修复错误的一部分，如果你的结束是在循环之外而不是在它内部，但它不会发现你没有改变你的循环计数器 . 请尝试使用此代码 . 它实际上几乎与你的几个小改动相同 .

  Option Explicit
  Sub test()
  Dim sht As Worksheet, i As Long, l As Long, j
  
  Dim nRows As Integer
  Dim CurYear As Date
  Dim StartDate As Date
  
  Set sht = Worksheets("Test1") ' set the sheet as object isntead of selecting it for faster code and avoiding other issues
  
  nRows = Cells(sht.Rows.Count, "B").End(xlUp).Row 'Last used row in column B - current region lastrow gets twitchy in some circumstances and should be avoided unless there is a reason to use it
  l = 41
  
  CurYear = range("I265").Value
  StartDate = range("M5").Value
  
  For i = 1 To nRows
    If Cells(i, 1).Value < CurYear And Cells(i, 1).Value > StartDate Then 'for If statements you use "and" not "&"
    Cells(l, 15) = Cells(i, 2) 'you will want something like this line and the next if you don't want to overwrite H41 if there is more than one match
    l = l + 1
    End If
  Next i
  
  End Sub
  

  另外，为了帮助调试，请打开本地窗口（在VBE中查看） . 使用F8逐步执行代码，在本地窗口中查看变量，以确保它们符合您希望它们在脚本中的该步骤 .

  如果您对代码执行此操作，您将看到错过了针对循环的变量的计数器更改 . 所以它一直在寻找nRow最终成为“”但它仍然保持在它设置的任何地方 . 无限循环 . 我将其更改为下一个格式，但是6个为1，半打为另一个格式 .

  欢迎来到VBA . 不要捅眼睛 . :-)

  回复于 2024-04-28T01:32:54+08:00