Instring Replace: Error

[YellowLabPro]

My nightmare will not end w/ these fractional values
Here is a new one: Error- Compile error. Expected Variable or Procedure, not Module
Grrrrrrrrrrrrrrggggggggggggghhhhhhhh!!!!

BTW: It errors on the first Replace on the first InStr line.

[vba]
With Wsf
For Each c In Range("M4:M" & LRowF)
If InStr(c.Value, "39098") > 0 Then c.Formula = Replace(c.Value, "39098", "1/16")
If InStr(c.Value, "39090") > 0 Then c.Formula = Replace(c.Value, "39090", "1/8")
If InStr(c.Value, "39149") > 0 Then c.Formula = Replace(c.Value, "39149", "3/8")
If InStr(c.Value, "39086") > 0 Then c.Formula = Replace(c.Value, "39086", "1/4")
If InStr(c.Value, "39084") > 0 Then c.Formula = Replace(c.Value, "39084", "1/2")
If InStr(c.Value, "39210") > 0 Then c.Formula = Replace(c.Value, "39210", "5/8")
If InStr(c.Value, "39145") > 0 Then c.Formula = Replace(c.Value, "39145", "3/4")
If InStr(c.Value, "39271") > 0 Then c.Formula = Replace(c.Value, "39271", "7/8")
Exit For
Next c
End With
[/vba]

[malik641]

Hey Doug,

Some things about your code:

What's Wsf? And you don't use it in the code you gave at all within the With statement. I'm assuming it's a worksheet object and if so, then when you say Range("M4:M" & LRowf) you should have a dot (.) in front of "Range" --> ".Range(...)"

You shouldn't have the Exit For statement in there. Your code will not loop at all because the Exit For statement is not enclosed by a separate condition.

I'm not sure I would use c.Value inside the Instr() function because it is expecting a string and c.Value is returning a Variant [check out VBA help on that] (I'm pretty sure it will behave ok, but I don't like to use variants if I don't have to). I would force it to convert to a string like so:
[vba]Instr(1, CStr(c.Value), "String2")[/vba]
The same applies for the Replace function, it expects 2 string values. This could be the cause of your error...

Why are you using .Formula rather than .Value when you are not inserting a formula?

[YellowLabPro]

Hi Joseph,
I tried IM you a little while ago to say whats up.
Thanks for the response. I did not include the entire code, only a block.
I found the one error, there was another procedure in the same Project titled "Replace" and this was causing the first issue. But there are few others now.
Here is my full block. Wsf is the worksheet name, I used a variable to declare it.
This is still a subset of my full code. I have pulled this out for testing. This works in a different Project where there seems to be no other code that might cause a conflict.
I did remove the Exit For, found that what you had brought up to be that exact case.

[VBA]
Sub FormatFrac()
Dim Wsf As Worksheet, Wsv As Worksheet
'Dim i As Integer
Dim LRowF As Long, LRowV As Long
'Dim Item As Variant, Num As Variant
Dim FF As String, VB As String
Dim c As Range
FF = "PCCombined_FF"
VB = "PCCombined_VB"
Set Wsf = Worksheets(FF)
Set Wsv = Worksheets(VB)
LRowF = Wsf.Cells(Rows.Count, 1).End(xlUp).Row
LRowV = Wsv.Cells(Rows.Count, 1).End(xlUp).Row
'For i = 4 To LRowF
' For Each Num In Array(39098, 39090, 39149, 39086, 39084, 39145, 39210, 39271) 'Corresponding Dates- :39098= 1/16: :39090= 1/8: :39149= 3/8: :39086= 1/4: :39084= 1/2: :39210= 5/8: :39145= 3/4: :39271= 7/8:
' If (Cells(i, "M").Value = Num) Then
' Cells(i, "M").NumberFormat = "# ?/?"
' 'If Cells(i, "M").NumberFormat = "# ?/?" Then
' 'Cells(i, "M").Interior.Color = vbYellow
' Exit For
' End If
'
' Next Num
'Next i
With Wsf
For Each c In Range("M4:M" & LRowF)
If InStr(c.Value, "39098") > 0 Then c.Formula = Replace(c.Value, "39098", "1/16")
If InStr(c.Value, "39090") > 0 Then c.Formula = Replace(c.Value, "39090", "1/8")
If InStr(c.Value, "39149") > 0 Then c.Formula = Replace(c.Value, "39149", "3/8")
If InStr(c.Value, "39086") > 0 Then c.Formula = Replace(c.Value, "39086", "1/4")
If InStr(c.Value, "39084") > 0 Then c.Formula = Replace(c.Value, "39084", "1/2")
If InStr(c.Value, "39210") > 0 Then c.Formula = Replace(c.Value, "39210", "5/8")
If InStr(c.Value, "39145") > 0 Then c.Formula = Replace(c.Value, "39145", "3/4")
If InStr(c.Value, "39271") > 0 Then c.Formula = Replace(c.Value, "39271", "7/8")
'Exit For
Next c
End With
'[Wsv].Activate
'With Wsv
' For Each c In Range("M4:M" & LRowV)
'If InStr(c.Value, "39098") > 0 Then c.Formula = Replace(c.Value, "39098", "1/16")
'If InStr(c.Value, "39090") > 0 Then c.Formula = Replace(c.Value, "39090", "1/8")
'If InStr(c.Value, "39149") > 0 Then c.Formula = Replace(c.Value, "39149", "3/8")
'If InStr(c.Value, "39086") > 0 Then c.Formula = Replace(c.Value, "39086", "1/4")
'If InStr(c.Value, "39084") > 0 Then c.Formula = Replace(c.Value, "39084", "1/2")
'If InStr(c.Value, "39210") > 0 Then c.Formula = Replace(c.Value, "39210", "5/8")
'If InStr(c.Value, "39145") > 0 Then c.Formula = Replace(c.Value, "39145", "3/4")
'If InStr(c.Value, "39271") > 0 Then c.Formula = Replace(c.Value, "39271", "7/8")
''Exit For
'Next c
'End With
End Sub
[/VBA]

[YellowLabPro]

The point about the variant- If I am not mistaken here, I do not want it to convert to a string; if converting them to a string makes them text.
What is happening is I have some fractional values, 1/16, 1/8, 1/2 etc... I need to convert these values which are generated by a formula in col. M to values, which are still numeric.
Once I do convert them to values, they change to a serial number date..... Grrrhhhhh
So the only way to get them back to a fractional value is to do an InStr replace.

[YellowLabPro]

My new error:
Compile error:
Wrong number of arguments or invalid property assignment.

As I said, this code works outside of this project whilst housed in another project.

[malik641]

Yeah I've missed you a few times with IMing myself. No biggie, I'm going to bed early tonight anyhow...probably after this post (or maybe a few more...just to satisfy my VBAX fix )


Ok. For the quick fix you're trying to do (I assume you have saved over what you could have salvaged and start over), try replacing that For Next with:
[VBA]For Each c In .Range("M4:M" & LRowF)
Select Case CStr(c.Value) ' Convert to String just to be sure
Case "39098"
c.Value = (1 / 16)
Case "39090"
c.Value = (1 / 8)
Case "39149"
c.Value = (3 / 8)
Case "39086"
c.Value = (1 / 4)
Case "39084"
c.Value = (1 / 2)
Case "39210"
c.Value = (5 / 8)
Case "39145"
c.Value = (3 / 4)
Case "39271"
c.Value = (7 / 8)
End Select
Next c[/VBA]

See if that works for you. You didn't need the Replace function to begin with. The preceding If statement verifies the condition to meet with the Instr() function, which you also didn't really need because the values you were using were exact date serial values. You would normally use the Instr() function to see if there is text contained within a string. The return value of Instr() is the character position where the match was found. In your case, you were using exact values, so you can use the Select Case method, which is more efficient for this situation since you are eliminating unnecessary checks with multiple If statements (with this code, once it finds a match, it will perform the underlying code beneath it and then move on to the next c variable, rather than checking the other conditions that follow up to the Next c statement).

Also, never choose a keyword from either VBA (i.e. Replace) or any object model currently available (i.e. Excel.object) for your Variables, Module names, Procedure names, etc. You can get into a lot of trouble and aggravation that way, as you've already been through

[YellowLabPro]

I will give this a go.
I did not know Replace was a keyword.... there should be a little guy w/ wings on his shoulders and Newbie halo above waving his magic wand warning of guys of such dangers....
Love this little guy.... >>

[malik641]

I will give this a go.
I did not know Replace was a keyword.... there should be a little guy w/ wings on his shoulders and Newbie halo above waving his magic wand warning of guys of such dangers....

- OR -

Love this little guy.... >>

Me too. That's how most people find me. During lunch break at work, at home, at school. I'm usually reading a technical book or online at the forums. Most of it had to do with school. But since the summer started (end of May) I look like this with my VB.NET, SQL, and Excel books
And I do manage to squeeze in some exercise as well

[rory]

PS If you are using Replace you don't need the InStr check anyway - if the text isn't there, Replace just won't replace anything.
Also, considering how I guess those values got there, I think you could use:
[VBA] For Each c In ActiveSheet.Range("M4:M" & LRowF)
Select Case CStr(c.Value) ' Convert to String just to be sure
Case "39098", "39090", "39149", "39086", "39084", "39210", "39145", "39271"
c.Value = Evaluate(Format$(c.Value, "m/dd"))
End Select
Next c
[/VBA]
Regards,
Rory

[malik641]

[vba]c.Value = Evaluate(Format$(c.Value, "m/dd"))[/vba]

That's real clever rory Nice code.

[YellowLabPro]

Ok Guys-
First- Thanks for all the help, Joseph and Rory!

Second- My initial post was regarding the compile error- looking for a variable or procedure, not a module. I want to post back what I found the problem to be gratus of Malik641 and Xld-

The problem was I had a Subroutine named "Sub Replace ()" . But the sub name was not the module name, no, it was a Sub name in the same PROJECT and this caused the conflict. Now the error message makes sense, but last night after a long day at the keyboard it was like reading Latin (Sorry Bob- have not mastered that yet either.... but it is on the list!) So my sub was trying to call another Sub! Ok- so I have this worked out now. Both Xld and Malik have cautioned me about this now and I BELIEVE them, really it was never about doubt, I really did not know that "Replace" was a keyword, nor to the extent of its reach when a keyword is used in a Sub name.

Third- Now regarding Malik and Rory's advice for improving my code. I would like to reply to them independently to keep points straight, as not to cross posts confusing items.

I will post to Malik first.

Cheers,

Doug

[rory]

Thanks!

[YellowLabPro]

1.

What's Wsf? And you don't use it in the code you gave at all within the With statement. I'm assuming it's a worksheet object and if so, then when you say Range("M4:M" & LRowf) you should have a dot (.) in front of "Range" --> ".Range(...)"

This was given to me by a boardmember. It seems to make sense not to need a .Range because it is in a Loop. This works as is. But I will try it w/ a .Range for confirmation purposes.
[VBA]With Wsf
For Each c In Range("M4:M" & LRowF)[/VBA]

2.

You shouldn't have the Exit For statement in there. Your code will not loop at all because the Exit For statement is not enclosed by a separate condition.

Yes I found this to be the case and removed it, I only found this out because Xld showed me how to use the Locals Window.
I will be researching the use of Exit For, I do not know how this works... meaning I do know that it is an early exit, but not how it should be used....

3.

I'm not sure I would use c.Value inside the Instr() function because it is expecting a string and c.Value is returning a Variant [check out VBA help on that] (I'm pretty sure it will behave ok, but I don't like to use variants if I don't have to). I would force it to convert to a string like so:

VBA:
Instr(1, CStr(c.Value), "String2")

VBA tags courtesy of www.thecodenet.com

Though it might be a variant, is .Value not the actual value that is contained in a Cell in the Worksheet? From the way I understood the code it was looking in the cell at the value.... c.Value.

Now are you saying that CStr is narrowing the parameter to a string value only?

Is alpha and numerical terms considered strings or is a numeric term considered a value?

4.

The same applies for the Replace function, it expects 2 string values. This could be the cause of your error...

As I posted in my previous thread, this is not the cause- it was a Sub Name conflict.

5.

Why are you using .Formula rather than .Value when you are not inserting a formula?

Cannot give an intelligent/correct answer- The c.Formula seemed appropriate because of the nature of the Replace function, that since it was a function, it would be a Formula.

[YellowLabPro]

BTW:
Here is my working code:
[VBA]
Sub FormatFrac()
Dim Wsf As Worksheet, Wsv As Worksheet
Dim i As Integer
Dim LRowF As Long, LRowV As Long
Dim Item As Variant, Num As Variant
Dim FF As String, VB As String
Dim c As Range
FF = "PCCombined_FF"
VB = "PCCombined_VB"
Set Wsf = Worksheets(FF)
Set Wsv = Worksheets(VB)
LRowF = Wsf.Cells(Rows.Count, 1).End(xlUp).Row
LRowV = Wsv.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To LRowF
For Each Num In Array(39098, 39090, 39157, 39086, 39218, 39149, 39279, 39084, 39341, 39210, 39145, 39271, 39335, 39398)
'Corresponding Num- 39098 39090 39157 39086 39218 39149 39279 39084 39341 39210 39145 39271 39335 39398
' 1/16 1/8 3/16 1/4 5/16 3/8 7/16 1/2 9/16 5/8 3/4 7/8 9/10 11/12
If (Cells(i, "M").Value = Num) Then
Cells(i, "M").NumberFormat = "# ??/??"
Exit For
End If
Next Num
Next i
With Wsf
For Each c In .Range("M4:M" & LRowF)
'For Each c In Intersect(Wsf.Columns(13), Wsf.UsedRange)
If InStr(c.Value, "39098") > 0 Then c.Formula = Replace(c.Value, "39098", "1/16")
If InStr(c.Value, "39090") > 0 Then c.Formula = Replace(c.Value, "39090", "1/8")
If InStr(c.Value, "39157") > 0 Then c.Formula = Replace(c.Value, "39157", "3/16")
If InStr(c.Value, "39086") > 0 Then c.Formula = Replace(c.Value, "39086", "1/4")
If InStr(c.Value, "39218") > 0 Then c.Formula = Replace(c.Value, "39218", "5/16")
If InStr(c.Value, "39149") > 0 Then c.Formula = Replace(c.Value, "39149", "3/8")
If InStr(c.Value, "39279") > 0 Then c.Formula = Replace(c.Value, "39279", "7/16")
If InStr(c.Value, "39084") > 0 Then c.Formula = Replace(c.Value, "39084", "1/2")
If InStr(c.Value, "39341") > 0 Then c.Formula = Replace(c.Value, "39341", "9/16")
If InStr(c.Value, "39210") > 0 Then c.Formula = Replace(c.Value, "39210", "5/8")
If InStr(c.Value, "39145") > 0 Then c.Formula = Replace(c.Value, "39145", "3/4")
If InStr(c.Value, "39271") > 0 Then c.Formula = Replace(c.Value, "39271", "7/8")
If InStr(c.Value, "39335") > 0 Then c.Formula = Replace(c.Value, "39335", "9/10")
If InStr(c.Value, "39398") > 0 Then c.Formula = Replace(c.Value, "39398", "11/12")
Next c
End With

[Wsv].Activate
With Wsv
For Each c In .Range("M4:M" & LRowV)
'For Each c In Intersect(Wsf.Columns(13), Wsf.UsedRange)
If InStr(c.Value, "39098") > 0 Then c.Formula = Replace(c.Value, "39098", "1/16")
If InStr(c.Value, "39090") > 0 Then c.Formula = Replace(c.Value, "39090", "1/8")
If InStr(c.Value, "39157") > 0 Then c.Formula = Replace(c.Value, "39157", "3/16")
If InStr(c.Value, "39086") > 0 Then c.Formula = Replace(c.Value, "39086", "1/4")
If InStr(c.Value, "39218") > 0 Then c.Formula = Replace(c.Value, "39218", "5/16")
If InStr(c.Value, "39149") > 0 Then c.Formula = Replace(c.Value, "39149", "3/8")
If InStr(c.Value, "39279") > 0 Then c.Formula = Replace(c.Value, "39279", "7/16")
If InStr(c.Value, "39084") > 0 Then c.Formula = Replace(c.Value, "39084", "1/2")
If InStr(c.Value, "39341") > 0 Then c.Formula = Replace(c.Value, "39341", "9/16")
If InStr(c.Value, "39210") > 0 Then c.Formula = Replace(c.Value, "39210", "5/8")
If InStr(c.Value, "39145") > 0 Then c.Formula = Replace(c.Value, "39145", "3/4")
If InStr(c.Value, "39271") > 0 Then c.Formula = Replace(c.Value, "39271", "7/8")
If InStr(c.Value, "39335") > 0 Then c.Formula = Replace(c.Value, "39335", "9/10")
If InStr(c.Value, "39398") > 0 Then c.Formula = Replace(c.Value, "39398", "11/12")
Next c
End With
[Wsf].Activate
[a1].Select
[D4].Select
[/VBA]

[rory]

I think this would achieve the same thing:
[VBA]Sub FormatFrac()
Dim Wsf As Worksheet, Wsv As Worksheet
Dim i As Integer
Dim LRowF As Long, LRowV As Long
Dim Item As Variant, Num As Variant
Dim FF As String, VB As String
Dim c As Range
Dim varNums()
FF = "PCCombined_FF"
VB = "PCCombined_VB"
Set Wsf = Worksheets(FF)
Set Wsv = Worksheets(VB)
LRowF = Wsf.Cells(Rows.Count, 1).End(xlUp).Row
LRowV = Wsv.Cells(Rows.Count, 1).End(xlUp).Row
varNums = Array(39098, 39090, 39157, 39086, 39218, 39149, 39279, 39084, 39341, 39210, 39145, 39271, 39335, 39398)
' 1/16 1/8 3/16 1/4 5/16 3/8 7/16 1/2 9/16 5/8 3/4 7/8 9/10 11/12
With Wsf
For i = 4 To LRowF
If UBound(Filter(varNums, .Cells(i, "M").Value)) >= 0 Then
.Cells(i, "M").Value = Evaluate(Format$(.Cells(i, "M").Value, "m/dd"))
.Cells(i, "M").NumberFormat = "# ??/??"
End If
Next i
End With
With Wsv
For Each c In .Range("M4:M" & LRowV)
If Len(c.Value) > 0 Then
If UBound(Filter(varNums, c.Value)) >= 0 Then
c.Value = Evaluate(Format$(c.Value, "m/dd"))
c.NumberFormat = "# ??/??"
End If
End If
Next c
End With
[D4].Select
End Sub
[/VBA]

Regards,
Rory

[YellowLabPro]

Thanks Rory!
I am going to hold off from changing my code just yet. I want to step through this so that I may understand better. My goal is to learn how to code.
As I work through this and solve for the points Malik and you have raised, I will replace my code w/ yours..... this will help me most. I am fortunately/unfortunately a very linear thinking person. So it helps for me to solve for each along the way.

But please note, very anxious to use the suggestions you have given.

Best,

Doug

[rory]

Doug,
No worries - let us know if you have any questions.
Re your .Range versus Range issue, the Range(...) syntax works on the activesheet, whatever that may be; .Range(...) works on the sheet you specified, regardless of which sheet is active. Same applies to .Cells versus Cells
HTH
Rory

[rory]

PS In your code, you seemed to loop through the same range twice for the first sheet, so I changed that to do all checks in one pass through.

[YellowLabPro]

Thanks Rory,
Waiting for Malik's answers and will continue from there.
The .Range point- nice good to know this.
The double loop, there are two sheets, Wsf and Wsv- that is the reason for the second loop. I did however find that I do not have the array in the second loop.

[malik641]

I am in the process of responding to your bigger post above (post #13). It's just that there's an auditor here today and I don't want him seeing me on the web too much

Will post asap