Find and replace with excel macros

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

What I am trying to do is copy a cell from tab one (always the same cell) and then look for it in a column in another tab. Once I have found the unique row with that data in it I want to copy the entire row and paste special values the row, thereby removing formulae with numerous vlookups which are only used once.

We have a pricing spreadsheet so on updating the next date in the cycle in the cell mentioned above, the macros should go to the pricing spreadsheet, look for the date that was entered in the cell and then copy the row that has this date.

Can anyone help as basic macros I am ok with, this is out of my league!

1 reply

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Hi Rachel,

try to record a macro to see if you search a value in give cell and copy the row in which result found, what code is generated.
Hi there, yes I have done that but it does not store any code except the copy past special values. So although whilst recording the macros I copy the cell, go to the tab and search for it (and find it) and then I copy past special values, what the macros records is me going to the cell it finds the first date in (C22) and copying and pasting it. It does not store any searching. I have pasted the macros below (it does other stuff as well which works fine so I have just copied the bit in question):

ActiveWindow.LargeScroll ToRight:=-1
Sheets("Input Sheet").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Calculation").Select
Range("C22").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It should have given you search code too. Wonder why it did not. Here is the break down of your code

'you scolled the window
ActiveWindow.LargeScroll ToRight:=-1

' you selected this shee
Sheets("Input Sheet").Select

'you selected this cell
Range("B8").Select

'internal excel prep for copy
Application.CutCopyMode = False
'actual copy
Selection.Copy

'this sheet is selected
Sheets("Calculation").Select

'this cell on calc sheet is select
Range("C22").Select

'from last selected cell, till the last used cell on rights, the cells are selected
Range(Selection, Selection.End(xlToRight)).Select

'from last selected range, till the last used cell on rights, the cells are selected
Range(Selection, Selection.End(xlToRight)).Select

'internal excel prep for copy
Application.CutCopyMode = False
'actual copy
Selection.Copy

'actual paste as values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



How ever, at the best this is only a template. I think this appoach would be a tad better

Sub doFindAndPasteSpecial()

   Dim sInputSheet            As String
   Dim rngInputRange          As Range
   Dim sOutputSheet           As String
   Dim rngSearchRange         As Range
   Dim rngOutputRange         As Range
   
   
   sInputSheet = "Input Sheet"   'name of the input sheet
   sOutputSheet = "Calculation"  'name of the output/paste sheet
   
   'this is the cell where the value to be search is always
   Set rngInputRange = Sheets(sInputSheet).Range(Cells(8, "B"))
   
   'if the lookup cell is blank, then exit sub
   If (rngInputRange.Value = vbNullString) Then Exit Sub
   
   'setting the range to be searched on output sheet
   Set rngSearchRange = Sheets(sOutputSheet).Range("C1:C" & Rows.Count)
   
   With rngSearchRange
      Set rngOutputRange = Nothing
      'attempting to find the cell that has the value
      Set rngOutputRange = .Find(What:=sInputRange.Value, _
                                 After:=.Cells(1, 1), _
                                 Lookat:=xlWhole, _
                                 SearchDirection:=xlNext, _
                                 MatchCase:=False)
      
      'no dice. getting out of routine
      If (rngOutputRange Is Nothing) Then Exit Sub
      
   End With
   
   'EUREKA!!!!!  FOUND IT
   With Sheets(sOutputSheet).Rows(rngOutputRange.Row)
      Application.CutCopyMode = False
      .Copy 'copying the row
      'pasting the row back
      .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
   End With
End Sub