Replace function syntax problem 1

[abitslow]

Hi there,
I am trying to write something that will look down a column and replace any instances of text such as "Gas show" with "Gas Show". (As I have to do this regularly and for a large number of text items I was trying not to use the Replace funtion in the menu but write a macro I can use every time.)

My attempt is:

Worksheets("Replace").Columns("A").Replace(What:="Gas shows",Replacement:="Gas Shows",SearchOrder:=xlByColumns,MatchCase:=True)

but it doesn't work. Where am I going wrong?

Many thanks in advance.
Matt.

 

[SkipVought]

Hi,

Loos the parentheses

Worksheets("Replace").Columns("A").Replace What:="Gas shows", Replacement:="Gas Shows", SearchOrder:=xlByColumns, MatchCase:=True

Have you tried turning on your macro recorder and doing an Edit/Replace?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[Zathras]

This is a common error when first working with functions.

There are two general ways to express a function with arguments:

x = SomeFunction(arg1, arg2, arg3)

and

SomeFunction arg1, arg2, arg3

Generally, a function is used to return a value. When the value is not needed, you must not use the parentheses. It seems silly, but that is the way VBA works.

So, you can either assign the result of the function call to a variable:

x = Worksheets("Replace").Columns("A").Replace(What:="Gas shows",Replacement:="Gas Shows",SearchOrder:=xlByColumns,MatchCase:=True)

or you can simply remove the parentheses:

Worksheets("Replace").Columns("A").Replace What:="Gas shows",Replacement:="Gas Shows",SearchOrder:=xlByColumns,MatchCase:=True

 

[abitslow]

Many thanks both. Much improved!

 

[DaveInIowa]

Would it help to generalize the replacement with something like:

Public Sub ProperCase()
    Dim ColA As Range
    
    For Each ColA In Range("A:A")
        ColA = WorksheetFunction.Proper(ColA)
    Next ColA
    
End Sub

 

[SkipVought]

Au contraire, David.

Although your solution does the same thing, it took about 4 times longer for an entire column of data

[tt]
0.62109375
2.41015625
[/tt]



Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[strongm]

>When the value is not needed, you must not use the parentheses

Or use 'Call'

 

[DaveInIowa]

Skip,
2.5 seconds versus any number of hard-coded Replace functions? I can wait .

Besides, if this was production code, I'd do something like intersect column A with UsedRange or place the edit in the Worksheet_Change event. I merely wanted to point out that there exists a worksheet function that may solve the problem in question.

 

[SkipVought]

One small process -- no biggie.

But when you have lots of data and more complex code, this kind of decision can make a big difference in processing time.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[1KN]

Hello All; this is my first post so I hope I am doing it right. A little background on my problem: I have 30+ spreadsheet "databases" (mere tables) of data that has been sorted and separated by type. These will be posted to the web via a group web site (which I am also coding for... old-fashioned DHTML).

I have created a user-friendly form in VBA with CommandButtons that are each linked to a specific sheet of data. When a button is clicked, the user can view the sheet they desire. I have created an additional CommandButton which is attached to a macro which runs a standard deviation calculation, based on which sheet is selected (i.e. if sheet 5A is selected, the calculation is run for the data range in sheet 5A).

Now my problem is that I cannot get the data value to be returned in the UserForm in a textbox I designated. Is it possible in VBA to have a calculation run in the background & a form in the foreground which will return a summary value (e.g. standard deviation) per form? Do I need to switch to Access? I would rather stick w/ XL if at all possible.

Thank you for your time, funny wine joke btw Skip.
Take care, 1KN

 

[SkipVought]

1KN,

Welcome to Tek Tips!

Please post your question in a new thread. It will not be answered in this thread.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[abitslow]

Thanks again both. I shall work through quite what the differences are and see what works best.
m