VBA Loop for copy and paste with search

gg452

New Member
Joined
Jun 17, 2013
Messages
6
Hi There,

Thank you in advance for taking the time to read this. I've tried to search for the answer but to no avail so I'm hoping some very kind person will be able to help put me out of my misery.

I'm trying to search for a specific term within a column of a worksheet, if that is found, execute a copy and paste (in the row above), then move and search for the next term up to 1,000 rows.

The frustrating part is that it executed perfectly once, then is giving me a 1004 error and I can't figure out why the error is occurring.

I appreciate any help I can get on this.

VBA Code:
Sub NewMonthAlt()
    Dim i As Integer
    Dim startRange As Integer, endRange As Integer
    Dim rng As Range
    Dim searchWord As String
    Dim rngPaste As Range
    Dim startpaste As Integer
        searchWord = "XX"
    

    With Sheets("Models")

        For i = 1 To .Cells(Rows.Count, 1000).End(xlUp).Row
            If .Range("C" & i) = searchWord Then   'Here it notes the row where we first find the search word
                startRange = i
                Do Until .Range("C" & i) <> searchWord
                    i = i + 1 'Here it notes the first time it stops being that search word
                startpaste = i - 2
                Loop
                endRange = i - 1 'Backtracking by 1 because it does it once too many times
                Exit For
            End If
        Next


        'Select range to copy
        Set rng = ActiveSheet.Range("D" & startRange & ":O" & endRange)
        
        Set rngPaste = ActiveSheet.Range("D" & startpaste & ":O" & startpaste)
        
        rng.Copy

        rngPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Paste it to the address set


    End With
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I am trying to understand your code. You copy a range and paste in another range.
But really what do you need.
If you had some data like the ones shown below, what should the macro do?
Or better yet, if you explain what data you have and what you need?

Dante Amor.xlsm
ABCDEFGHIJKLMNO
1A1B1XXD1E1F1G1H1I1J1K1L1M1N1O1
2A2B2XXD2E2F2G2H2I2J2K2L2M2N2O2
3A3B3XXD3E3F3G3H3I3J3K3L3M3N3O3
4A4B4D4E4F4G4H4I4J4K4L4M4N4O4
5A5B5D5E5F5G5H5I5J5K5L5M5N5O5
6A6B6D6E6F6G6H6I6J6K6L6M6N6O6
7A7B7XXD7E7F7G7H7I7J7K7L7M7N7O7
8A8B8XXD8E8F8G8H8I8J8K8L8M8N8O8
9A9B9XXD9E9F9G9H9I9J9K9L9M9N9O9
Models
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,714
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top