For-Next Loop Help

[YellowLabPro]

I have to search through column F for the terms "Hat" or "Shoe". If it finds either term, then convert from Decimal format to Fractional format if the value is not a whole number.

I am solving for each stage and have questions along the way. This may jump around some as I will use what I know to assist me in reaching my final product.
This will not be marked as "Solved" for a while, as I will confine my questions to this post to keep continuity.


Q1# In Code syntax I want to add an "Or" condition my current loop is fine until I add the Or "Shoe"

[vba]For i = 1 To Lrow
If Cells(i, "F") = "Hat" Or "Shoe" Then
Cells(i, "M").Value = Cells(i, "M") / 2
End If
Next i[/vba]

Thanks for any assistance...
Doug

[rory]

Hi Doug,
You can either use:
[VBA]For i = 1 To Lrow
If Cells(i, "F") = "Hat" Or Cells(i, "F") = "Shoe" Then
Cells(i, "M").Value = Cells(i, "M") / 2
End If
Next i[/VBA]

or you can use:
[VBA]For i = 1 To Lrow
Select Case Cells(i, "F").Value
Case "Hat", "Shoe"
Cells(i, "M").Value = Cells(i, "M") / 2
End Select
Next i[/VBA]

Regards,
Rory

[YellowLabPro]

Very nice,
Thanks Rory-

[Charlize]

A for next (probably overbloated, but you said a For Next loop) :[VBA]For i = 1 To lrow
For Each Item In Array("Hat", "Shoe")
If Cells(i, "F") = Item Then
Cells(i, "M").Value = Cells(i, "M") / 2
Exit For
End If
Next Item
Next i[/VBA]

[YellowLabPro]

Thanks Charlize-
Item is coming back as variable not declared-
How would you declare this?

[Tommy]

Double whammy

[vba]
Dim Item As String
For i = 1 To lrow
For Each Item In Array("Hat", "Shoe")
If Cells(i, "F") = Item Then
Cells(i, "M").Value = GenFraction(Cells(i, "M") / 2, True)
Exit For
End If
Next Item
Next i
[/vba]

[YellowLabPro]

Tommy,
#1- Compile error:
For Each control variable mst be Variant or Object

My Goal is this-(restated for clarity)
Look for "Hat" or "Shoe" (We have this)
If finds either and value is in decimal format and is not a whole number then convert to fractional.

regards,

[YellowLabPro]

Not my actual final goal, but laying out the logic- here is what I have. My code errors at the "and"

[VBA]
For i = 1 To Lrow
If Cells(i, "F") = "Hat" Or Cells(i, "F") = "Shoe" _
and if Cells(i, "M").numberformat = "0.0" Then
Cells(i, "M").interiorfill = vbYellow
End If
End If
Next i
[/VBA]

[Tommy]

To Fix what you have:
[vba]
For i = 1 To 28
If (Cells(i, "F") = "Hat" Or Cells(i, "F") = "Shoe") And Cells(i, _
"M").NumberFormat = "0.0" Then
Cells(i, "M").Interior.ColorIndex = 6 'search for PatternColorIndex
Cells(i, "M").NumberFormat = "# ??/??" '10.75 displays as 10 3/4
If Cells(i, "M").Value - Int(Cells(i, "M").Value) Then
Cells(i, "M").Value = Cells(i, "M").Value / 2
End If
End If
Next i
[/vba]
This will make it check for "Hat" or "Shoe" and the number format has to be "0.0". "0.00" will not get picked up. Then it change the format to "# ??/??" for a fractional display and divides the value by 2 if there is a value in Column "M" that has a decimal value.

EDIT: Didn't finish the post before I submit LOL

[YellowLabPro]

Tommy,
I think I have you all over the place... apologies.
The color and the divison were only for testing the If Condition.
As I posted originally- I was going to bounce around- not my typical approach.... but I thought I would try and solve for certain pieces of the procedure as they were solved and then post back for help. I think I have made spaghetti out of it.
Some of your code should apply- But if we could start from here and I will try and stay on point...
Here is what I have:
[VBA]
For i = 1 To Lrow
If Cells(i, "F") = "Hat" Or Cells(i, "F") = "Shoe" Then
Cells(i, "M").Formula = Cells(i, "M") / 1
If Cells(i, "M").NumberFormat <> "0" Then
Cells(i, "M").(convert to fraction) psuedo code
End If
End If
Next i
[/VBA]

This line is only dealing w/ the number.
[vba]If Cells(i, "M").NumberFormat <> "0" Then [/vba]
What I actually need is the line above will perform the division to see if it is a whole number. If it is not a whole number we need to convert to a fraction.
I think you had some reference in your earlier posts that was dealing w/ that.

[YellowLabPro]

Tommy,
Running your code works. I should have tested first.... but was looking at some of the extra lines and thought they were not poignant.
I removed the /2 line and that provides me the answers I am looking for.
Right On!

There are couple of areas that I would like to explore further-
The spacing on fraction conversion looks a little wide- like two spaces inbetween the whole number and the fraction. Can this be made to look as if the numbers are closer?
I had to change the .NumberFormat to "0.000" to deal w/ .375 decimal values and this may have an effect on the appearance.

Maybe the answer is to convert to text and remove extra spaces.
I will monkey w/ this now until I hear back.

Thanks,

Doug

[YellowLabPro]

Ok-
Got everything dialed in! I just needed to change the "# ??/??" to "# ?/?".

I will give you time to reply. I have some other questions that I would like to explore.

[Charlize]

Thanks Charlize-
Item is coming back as variable not declared-
How would you declare this?

[VBA]Dim Item as Variant[/VBA]

[YellowLabPro]

Hi Charlize-
That was it- Thanks! I should have tried just dimming it like the message box had notified me....
Your solution took care of another issue where I am looking for a variation of the original term-!

Thanks to you and Tommy I have a nice working little procedure!
[VBA]
For i = 1 To Lrow
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY")
If Cells(i, "F") = Item Or Cells(i, "G") = Item And _
Cells(i, "M").NumberFormat = "0.000" Then
Cells(i, "M").Interior.ColorIndex = 6 'search for PatternColorIndex
Cells(i, "M").NumberFormat = "# ?/?" '10.75 displays as 10 3/4
Exit For
End If
Next Item
Next i
[/VBA]

[Tommy]

Good Morning
I would change the below
[VBA]
If Cells(i, "F") = Item Or Cells(i, "G") = Item And _
Cells(i, "M").NumberFormat = "0.000" Then
[/VBA]

To

[VBA]
If (Cells(i, "F") = Item Or Cells(i, "G") = Item) And _
Cells(i, "M").NumberFormat Like "*0.0*" Then
[/VBA]

The reason is If cells F or G = Item and the numberformat has 0.0 in it then ...... This will make it clear and less chance of unpredictable results. IMHO