Solved: If Instr line of code assistance needed

[YellowLabPro]

[vba]If InStr(.Cells(i, "J"), "Mm") > 0 Then c.Formula = Replace(.Cells(i, "J"), "Mm", "MM")[/vba]
This a bastardized formula from other work I have- the c.Formula is the tricky part.
It errors on the c.Formula
This will run against the following list- there will be multiple occurrences of "Mm". This is a shortened list- there will be hundreds....

J2 = ;Waterproof Level: 10000Mm;Breathability Level: 5000Mm;Type: Shell;
J3 = ;Waterproof Level: 10000Mm;Breathability Level: 5000Mm;Type: Shell;
J4 = ;Waterproof Level: 20000Mm;Breathability Level: 5000Mm;Type: Insulated
[vba]
For Each wsSourceSheet In Workbooks("TGSProductsAttribPrep.xls").Worksheets
With wsSourceSheet
lrwSource = lr(wsSourceSheet, "A")
For i = 2 To lrwSource
If Not IsEmpty(.Cells(i, "G").Value) Then
.Cells(i, "J").Value = ";" & .Range("G1").Value & .Cells(i, "G").Value
End If

If Not IsEmpty(.Cells(i, "H").Value) Then
.Cells(i, "J").Value = .Cells(i, "J").Value & ";" & .Range("H1").Value & .Cells(i, "H").Value
End If

If Not IsEmpty(.Cells(i, "I").Value) Then
.Cells(i, "J").Value = .Cells(i, "J").Value & ";" & .Range("I1").Value & .Cells(i, "I").Value
End If
Dim c As Range
.Cells(i, "J").Value = Application.WorksheetFunction.Proper(.Cells(i, "J").Value)
If InStr(.Cells(i, "J"), "Mm") > 0 Then c.Formula = Replace(.Cells(i, "J"), "Mm", "MM")

(c.Value, " Crocodile ", "Print")
Next i
i = 2
wsSourceSheet.Columns("A:K").AutoFit
End With
Next wsSourceSheet
[/vba]

[Norie]

Doug

As far as I can see you aren't actually defining/setting c to anything.

[Bob Phillips]

Should it be

[vba]

If InStr(.Cells(i, "J").VAlue, "Mm") > 0 Then .Cells(i, "J").Value= Replace(.Cells(i, "J"), "Mm", "MM")
[/vba]

and what is this line suppoed to be

[vba]

(c.Value, " Crocodile ", "Print")
[/vba]

[Jacob Hilderbrand]

You can just replace once on the entire column/range.

[VBA]
Range("A:A").Replace What:="MM", Replacement:="MM", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
[/VBA]

You could make it case sensitive and match just the Mm exactly as well.

[YellowLabPro]

Norie,
Yes.... that seems to be the case, I have yet to figure out how to write my code around that. I still have trouble correlating the range, c. in this case to the syntax....

[YellowLabPro]

Thanks Bob and Jake.....

Bob- Darn gremlins again----- I had that in my old stuff I copied to the page for example sake, copied the code to the board and missed deleting it. It is insignificant.
Thanks for the help

[YellowLabPro]

Bob,
Practical reasons?- What is the real difference between your's and Jake's method. I mean the If Instr is asking the question if it is there, and Replace is full on.... well, replace method.
I have been thinking about what scenario one would work and another would not, but not coming up w/ anything.

[rory]

If you were doing one cell at a time, there might be a slight speed improvement in avoiding changing the formula for each cell. But I suspect that a straight Replace on all cells will be much quicker.
FWIW.

[johnske]

Doug, look up the Replace method in the Help files, the very first sentence is "Finds and replaces characters in cells within the specified range", so if you use some sort of 'check' or 'Find' before replacing you're just duplicating things unnecessarily (and slowing your code down)

[YellowLabPro]

John,
It took a little bit for that to sink in. What I think you are saying is, using,
If InStr is checking and if true, then do something, in this case Replace a string, correct? Which is the extra checking step taking longer to process.
Where replace is basically checking and performing the action.

I hope this does not come off wrong- in my Help file, Replace Method on Range does not say that-
Mine states this-
"Returns a Boolean indicating characters in cells within the specified range. Using this method doesn’t change either the selection or the active cell."
Which confuses me, 1)I understand Boolean is basically an on/off switch, true/false, 0/1- what this means to me is if the string exists, true, then execute, if not, then false do not. 2)It states in the second sentence, "Using this method doesn’t change either the selection or the active cell."
But we know this is not as I am reading it because it does change the cell value. So I must be interpreting it wrong.

[johnske]

Replace Method


Finds and replaces characters in cells within the specified range. Using this method doesn?t change either the selection or the active cell.
For information about using the Replace worksheet function in Visual Basic, see Using Worksheet Functions in Visual Basic.

Syntax
expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte)
expression Required. An expression that returns a Range object.
What Required String. The string you want Microsoft Excel to search for.
Replacement Required String. The replacement string.
LookAt Optional Variant. Can be one of the following XlLookAt constants: xlWhole or xlPart.
SearchOrder Optional Variant. Can be one of the following XlSearchOrder constants: xlByRows or xlByColumns.
MatchCase Optional Variant. True to make the search case sensitive.
MatchByte Optional Variant. You can use this argument only if you?ve selected or installed double-byte language support in Microsoft Excel. True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents.

Remarks
The settings for LookAt, SearchOrder, MatchCase, and MatchByte are saved each time you use this method. If you don?t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

The replace method always returns True.

[Bob Phillips]

It will if selection or a ctivecell is the target range, else not.

[YellowLabPro]

John,
In my text help file there seems to be a crossover between the Range Object and the WorksheetFunction Object.
In the Range Object it states using a Range, which is what I am doing.

The WorksheetFunction Object instruct to use arguments, so the first half of the inital statement does agree w/ what you wrote, but then goes into this: "based on the number of characters you specify, with a different text string."
Which I have not specified any number of characters. So I would not have thought this would have been the correct Method to use- I have to ask if this is a difference in the Help File versions either one of us have? I think you are using Excel 2000?

As it applies to the WorksheetFunction:
Replaces part of a text string, based on the number of characters you specify, with a different text string.
expression.Replace(Arg1, Arg2, Arg3, Arg4)
expression Required. An expression that returns a WorksheetFunction object.
Arg1 Required String. Text in which you want to replace some characters.
Arg2 Required Double. The position of the character in Arg1 that you want to replace with Arg4.
Arg3 Required Double. The number of characters in Arg1 that you want the Replace method to replace with Arg4.
Arg4 Required String. Text that will replace characters in Arg1.

Apologies if this seems trivial, but I take this sort of think literally, the only way I know how to keep it straight in my head...

[rory]

I think it just means that it does not change which range is selected or active.

[johnske]

I think it just means that it does not change which range is selected or active.

I think that's what's meant also... Excel 2000 Help files here Doug

[YellowLabPro]

Thanks John,
I will have a go at them in a little bit. Thanks for posting them, though I was really just asking you if you were reading the same thing I was or maybe my intreptation was offbase. I still consider myself very inexperienced and fumbling through the basics, so pardon my slowness.