Copy & paste range once specific value in cell in reached

[thechad]

Greetings all,

I have a range from G2:G2000 that contains mixed values in the cells. There will be a point where the cells begin saying "No Match" and will continue to the end of the range doing so. I need a macro that will copy from G2 all the way to the last value just above the first cell that says "No Match". Once that is copied, I would like to take those values and paste them in cell A2 downwards but as "Values" only.

Can this be done? Assistance would be appreciated.

Thanks so much!

 

[portews]

Try this:

Sub NoMatch()
Dim i as Integer
'start with row 2
i = 2

'loop through each cell in G until you hit "No Match" or G2000
Do While i < 2001 And Not Cells(i, 7).Value = "No Match"
    'copy G to I in the same row
    Cells(i, 1) = Cells(i, 7)
    'increment the counter
    i = i + 1
'next row
Loop

End Sub

 

[thechad]

Try this:

Sub NoMatch()
Dim i as Integer
'start with row 2
i = 2

'loop through each cell in G until you hit "No Match" or G2000
Do While i < 2001 And Not Cells(i, 7).Value = "No Match"
    'copy G to I in the same row
    Cells(i, 1) = Cells(i, 7)
    'increment the counter
    i = i + 1
'next row
Loop

End Sub

Bill...this works awesome!!! Thank you so much for you help!

C