Selecting workbook and find/replace

Question

There is actually two questions in this post. Thank you in advance.

My first problem is that I cannot seem to get Excel to choose the Excel workbook that is named "(DATES)-Usage", where (DATES) changes each day.

Second problem is that the .Replace Object is giving me and Run-Time error '438': Object doesn't support this property method. Could someone please take a look and tell me where I am making my mistakes. Thank you again!

Sub UsersBySource_Update()

  ' Create variables
  Dim ws As Worksheet
  Dim wb As Workbook
  Dim unspecified As String
  Dim organic As String
  Dim TheString As String, Cohort As Date

  ' Ask user for cohort date and return an error if invalid date is entered
  TheString = Application.InputBox("Please Specify The Cohort:")

  If IsDate(TheString) Then
      Cohort = DateValue(TheString)
  Else
      MsgBox ("Invalid Date")
  End If

  ' Set variables
  unspecified = "[Unspecified]"
  organic = "Organic"

  ' Activate the workbook
  For Each wb In Workbooks
      If wb.Name Like "*-Usage" Then
          wb.Activate
      End If
  Next

  ' Find and Replace the unspecified and organic variables in the workbook
  ActiveSheet.Replace What:=unspecified, Replacement:=organic, LookAt _
  :=xlPart, SearchOrder:=xlByRows, MatchCase:=False

  ' Add Columns and Row to fix formatting to continue
  Columns("B:E").Insert shift:=xlToRight
  Range("B2").EntireRow.Insert

  ' Copy and Paste initial date range to start program
  Range("A3").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  Range("B3").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=True, Transpose:=False

End Sub

I believe the `Replace` method acts upon a range, not a worksheet. — Brian, Jun 29 '16 at 18:01
Now that you have your answer (yay!), I **highly** suggest reading through, and implementing, [avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). It will make your code tighter, less prone to errors, and will teach you how to work directly with your data. This will allow your macro to run faster as well. — BruceWayne, Jun 29 '16 at 18:10

score 1 · Accepted Answer · edited Jun 29 '16 at 18:06

1

You need to change

ActiveSheet.Replace What:=unspecified, Replacement:=organic, LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False

to the following

ActiveSheet.Cells.Replace What:=unspecified, Replacement:=organic, LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False

edited Jun 29 '16 at 18:06

Brian

2,078
1
15
28

answered Jun 29 '16 at 18:03

Kyle

2,543
2
16
31

Mátray Márk · Answer 2 · 2016-06-29T18:15:26.073

0

Now you said that you want a find a Sheet that's like (DATES)-Usage, but you're looking in Workbooks:

 For Each wb In Workbooks
      If wb.Name Like "*-Usage" Then
          wb.Activate
      End If
  Next

Try it like this: (edited)

 For Each ws In Worksheets
      If ws.Name Like "*-Usage" Then
          Exit For
      End If
  Next

Accoriding to MSDN: Replace, is a Range method.

So instead of this:

ActiveSheet.Replace What:=unspecified, Replacement:=organic, LookAt _
  :=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Try this:

ws.Cells.Replace What:=unspecified, Replacement:=organic, LookAt _
      :=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Cheers.

edited Jun 29 '16 at 18:15

answered Jun 29 '16 at 18:11

Mátray Márk

455
5
17

You mean `ws.Cells.Replace...`? I'd stay away from `ActiveSheet`, and use a variable instead . – BruceWayne Jun 29 '16 at 18:12
Sorry i did mean Workbooks. – itzmurd4 Jun 29 '16 at 18:12
You would have to quit the Foreach for that to work. If any other sheet follows the Date-Usage sheet it would try to use that one. I'll edit my comment to reflect that. – Mátray Márk Jun 29 '16 at 18:14
No worries. I found out the problem. It is a .csv file, so i just added. *-Usage.csv and it worked. Thank you though! – itzmurd4 Jun 29 '16 at 18:15
Workbook.Name should not return the extension though. – Mátray Márk Jun 29 '16 at 18:33

score 0 · Answer 3 · answered Jun 29 '16 at 18:37

Change these lines

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

to these lines

With ActiveSheet
    .Range(Range("A3"), Range("A3").End(xlDown)).Copy
    .Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True
End With
Application.CutCopyMode = False

You do not need all the Select methods.

Selecting workbook and find/replace

3 Answers3