VBA to return results to next blank row

[Pumper]

Hi All,

I am trying to lookup up a value "Medium" (Anywhere in column A) and if there cut the two values in column C & D and return to the next unused row (both remaining in Columns C & D) and Add "Sale" to column A.

This may be clearer:

Any help on this would be much appreciated.

Thank you in advance

 

[Peter_SSs]

1. Will there always be a "Medium" in column A?
   
   
2. Will there ever be more than one "Medium" in column A? If so, what should happen then?

 

[Pumper]

1. Will there always be a "Medium" in column A?
   
   
2. Will there ever be more than one "Medium" in column A? If so, what should happen then?

Hi Peter_SSs, no to both, thanks for looking!

 

[Peter_SSs]

Thanks for the clarifications. Try this with a copy of your data.

Sub MediumToBottom()
  Dim rMed As Range
  
  Set rMed = Columns("A").Find(What:="Medium", LookAt:=xlWhole, MatchCase:=False)
  If Not rMed Is Nothing Then
    With Range("A" & Rows.Count).End(xlUp).Offset(1)
      rMed.Offset(, 2).Resize(, 2).Cut Destination:=.Offset(, 2)
      .Value = "Sale"
    End With
  End If
End Sub

 

[Pumper]

Thanks for the clarifications. Try this with a copy of your data.

Sub MediumToBottom()
  Dim rMed As Range
 
  Set rMed = Columns("A").Find(What:="Medium", LookAt:=xlWhole, MatchCase:=False)
  If Not rMed Is Nothing Then
    With Range("A" & Rows.Count).End(xlUp).Offset(1)
      rMed.Offset(, 2).Resize(, 2).Cut Destination:=.Offset(, 2)
      .Value = "Sale"
    End With
  End If
End Sub

Brilliant that works perfectly, thank you so much for you quick response!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.