Excel Forum

Code: Select all

Sub FindAndPlaceValue()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim myVar As Range
Dim lr As Long
Dim findWhat As String, replaceWithWhat
Set myVar = [Select]
Set ws = Worksheets("in")
findWhat = myVar
replaceWithWhat = [Update]
lr = Cells(Rows.Count, 2).End(xlUp).Row
With ws.Range("A1:A" & lr)
   .AutoFilter Field:=1, Criteria1:=findWhat
   If .SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      ws.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).Offset(0, 4).Value = replaceWithWhat
   End If
   .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Code: Select all

Sheets("in").Range("a" & Range("j2").Value).Resize([Send].Rows.Count, _
    [Send].Columns.Count).Value = [Send].Value