CODEWITHSUNDEEP
vbaexcel
Reisenrich
Reisenrich

Reputation: 137

Using VBA If then statement to copy and paste data

I am a brand new VBA user attempting to copy and paste data based on a range of dates. In column one I have dates and in column two I have the data I would like to copy and paste. CurYear refers to the end date in the range I am looking for and StatDate refers to the beginning date in the Range I am looking for. When I run this code it crashes Excel. Please help I am very lost 

Worksheets("Weekly").Select

Dim nRows As Integer
Dim CurYear As Date
Dim StartDate As Date

nRows=Range("A1").CurrentRegions.Count.Rows
CurYear=Range("I265").Value
StartDate=Range("M5").Value

Do While Cells(nRows,1)<>""

if Cells(nRows,1).Value< CurYear & Cells(nRows,1)> StartDate Then

Cells(nRows,1).Offset(0,1).Copy
Worksheets("Weekly").Range("H41").Paste

Loop
End If

Upvotes: 0

Views: 15704

Answers (2)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

Instead of using copy/ paste that uses a lot of memory and makes the program run slow, you maybe want to consider the following code that serves the same purpose as your code or Rodger's yet faster than using Select and copy/ paste syntax.

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

Upvotes: 0

Rodger
Rodger

Reputation: 841

Put "option explicit" at the top of your code (before the sub) and it will tell you things to fix. Doing that will fix the part of your error where your end if was outside the loop instead of inside it but it won't catch that you weren't changing your loop counter. Try this code instead. It is actually pretty much the same as what you had with a couple minor changes. 

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

Also, to help with debugging, Open your locals window (View in the VBE). Step through your code with F8, watching your variables in the locals window to ensure that they are what you expect them to be at that step in your script.

If you do this with your code, you will see that you were missing a counter change with your variable for your loop. So it was looking for nRow to eventually be "" but it stays at whatever it was set to. Infinite loop. I changed it to a for next format but 6 of 1 and half dozen of another for your code.

Welcome to VBA. Don't poke yer eye out. :-)

Upvotes: 1

Related Questions