PDA

View Full Version : Solved: If Instr line of code assistance needed



YellowLabPro
09-13-2007, 03:25 PM
If InStr(.Cells(i, "J"), "Mm") > 0 Then c.Formula = Replace(.Cells(i, "J"), "Mm", "MM")
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

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

Norie
09-13-2007, 03:28 PM
Doug

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

Bob Phillips
09-13-2007, 03:32 PM
Should it be



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


and what is this line suppoed to be



(c.Value, " Crocodile ", "Print")

Jacob Hilderbrand
09-13-2007, 03:40 PM
You can just replace once on the entire column/range.


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


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

YellowLabPro
09-13-2007, 03:42 PM
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
09-13-2007, 03:44 PM
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
09-14-2007, 02:33 AM
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
09-14-2007, 02:56 AM
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
09-14-2007, 03:03 AM
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
09-14-2007, 03:32 AM
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
09-14-2007, 03:39 AM
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 (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\Office\1033\vbaxl9.chm ::/html/xlhowUsingWorksheetFunctions.htm).

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
09-14-2007, 03:41 AM
It will if selection or a ctivecell is the target range, else not.

YellowLabPro
09-14-2007, 03:53 AM
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 (mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaxl10. chm::/html/xlobjWorksheetFunction1.htm) 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
09-14-2007, 03:58 AM
I think it just means that it does not change which range is selected or active.

johnske
09-14-2007, 06:16 AM
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 (http://xlvba.3.forumer.com/index.php?showtopic=333) Doug

YellowLabPro
09-14-2007, 06:25 AM
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.