PDA

View Full Version : For-Next Loop Help



YellowLabPro
07-24-2007, 08:23 AM
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"

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

Thanks for any assistance...
Doug

rory
07-24-2007, 08:35 AM
Hi Doug,
You can either use:
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

or you can use:
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

Regards,
Rory

YellowLabPro
07-24-2007, 08:39 AM
Very nice,
Thanks Rory-

Charlize
07-24-2007, 03:45 PM
A for next (probably overbloated, but you said a For Next loop) :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

YellowLabPro
07-24-2007, 03:52 PM
Thanks Charlize-
Item is coming back as variable not declared-
How would you declare this?

Tommy
07-24-2007, 05:07 PM
Double whammy :devil2:


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

YellowLabPro
07-24-2007, 05:49 PM
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
07-24-2007, 05:55 PM
Not my actual final goal, but laying out the logic- here is what I have. My code errors at the "and"


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

Tommy
07-24-2007, 06:59 PM
To Fix what you have:

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

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
07-24-2007, 07:09 PM
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:

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


This line is only dealing w/ the number.
If Cells(i, "M").NumberFormat <> "0" Then
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
07-24-2007, 07:22 PM
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
07-24-2007, 07:36 PM
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
07-24-2007, 11:38 PM
Thanks Charlize-
Item is coming back as variable not declared-
How would you declare this?Dim Item as Variant

YellowLabPro
07-25-2007, 04:22 AM
Hi Charlize-
That was it- Thanks! I should have tried just dimming it like the message box had notified me....:banghead:
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!

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

Tommy
07-25-2007, 06:22 AM
Good Morning :)
I would change the below

If Cells(i, "F") = Item Or Cells(i, "G") = Item And _
Cells(i, "M").NumberFormat = "0.000" Then


To


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


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