Changes formula to value- wrong number format

[YellowLabPro]

This line changes the formula to a value I need for subsequent operations, however it changes a fraction 1/8 to a date of 8-Jan. I need it to remain 1/8.

.Range("M4:M" & LRowV).Formula = .Range("M4:M" & LRowV).Value

Hopefully a simple fix...: pray2:

Thanks.... (sorry for hitting the board so much, trying to finish this one piece) :whistle:

[Bob Phillips]

I cant reproduce that behaviour Doug, but maybe force it?



With .Range("M4:M" & LRowV)
.Formula = .Value
.NumberFormat = "# ?/?"
End With

[YellowLabPro]

xld,
Unfortunately I cannot alter the numberformat first. I will leave this open per your advice, but I am posting a different approach to see if there is another option.

[YellowLabPro]

Here is my current code.
This code will not recognize that .Value is a fraction w/ "?/?"
It will find a value like, .Value = 1
So If there is another property we can substitue in for "?/?".
I have tried greater than zero and less than 1, but this fails because there is a formula in the cell.


For i = 4 To LRowF
If Cells(i, "M").Value = "?/?" Then
Cells(i, "M").NumberFormat = "?/?"
End If
Next i


Thanks,

Doug

[Bob Phillips]

I don't understand, how can .Value be "?/?", that is a numberformat.

[YellowLabPro]

I was under the impression that "?/?" would be a wildcard search for a fractional value.
I spent a lot of time troubleshooting this y-day/l-night and worked out a solution. A big help was having the Locals window as a Resource- Amazing how much this helped me in needling down- Thanks for teaching me this!

Note*- If you would like to educate me on this specifically that would be great, but do not spend any of your time writing me any code. I worked out a very ugly solution last night and do not want you to waste any of your time on this currently.

I still need to grasp a lot of core items and this one shows that!

Thanks Bob,

Doug

[Bob Phillips]

Okay, I see what you are trying to do.

Test the Text property



If Cells(i, "M").Text Like "*?/?" Then

[YellowLabPro]

Hi Bob,
I implemented this, thought it would do the trick. But still hitting a brick wall.
I have had to change my numberformat for the cells to Fraction - "??/??" to accommodate a few values like 9/10, 11/12.
Due to this format style when I use the line of code
If Cells(i, "k").Text Like "*?/?" Then
It does not find any of the fractional values of single values fractions like 1/8 because it is formatted as "??/??"
Any other suggestions?
I am telling you, the 80/20 rule is pushing my buttons right now....


Sub testk()
Dim i As Integer
Dim lrow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
lrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To 20
If Cells(i, "k").Text Like "*?/?" Then
Cells(i, "k").NumberFormat = "General"
'Cells(i, "k").Interior.Color = vbYellow
End If
Next i
End Sub

[YellowLabPro]

Here are three versions of values I need to convert to their proper size format w/ regards to display and numberformat.
I keep bumping into a particular property of each numerical value which creates undesirable/incorrect values in my size column. All three are to be displayed w/ their fractional sizing.
If I use the format "?/?" then the second and third display correctly and the first one is truncated down.
If I use the format "??/??" then all three show the fractional values, but the second and third values display w/ extra space on the right and left hand margins and the third value has big gap between the integer and the fraction.

Reef Grom Jamaican Sandals Black/Yellow 11/12

Lucky Bolt Pack 7/8

32 Lashed Boots 06 Black/Red/Grey 9.5

Desired:
11/12
1/8
9 1/2

I have tried different combinations of the numberformat property, .Value, .Text and Like w/out success.

Thanks

Doug

[rory]

Does something like this work:
Function GetFractionFormat(strText As String) As String
Dim intSpace As Integer, intDivisor As Integer
Dim strNumerator As String, strDenominator As String
strText = Trim$(strText)
intSpace = InStr(strText, " ")
intDivisor = InStr(strText, "/")
strNumerator = String(Len(Trim$(Mid$(strText, intSpace + 1, intDivisor - intSpace - 1))), "?")
strDenominator = String(Len(Trim$(Right$(strText, Len(strText) - intDivisor))), "?")
GetFractionFormat = "# " & strNumerator & "/" & strDenominator
End Function
Sub SetFormats()
Dim rngcell As Range
For Each rngcell In Selection
With rngcell
If Len(.Value) > 0 Then
.NumberFormat = "# ??/??"
.NumberFormat = GetFractionFormat(.Text)
.Formula = .Value
End If
End With
Next rngcell
End Sub


adjusting the SetFormats routine to test on your data as necessary.

Regards,
Rory

[Bob Phillips]

I don't know what is happening here, but I keep making posts and they just disappear. Ah well!

I said earlier that a format of ??/?? is not Like *?/? whereas it is Like *?/?* - note the difference.

But I added that I was getting lost as to what you are trying to achieve. You seem to be setting formats, then clearing them, and I am not sure why.

[YellowLabPro]

Rory-
Yes this works on my test sheet. I will try and incorporate this to my worksheet. I will aprise....

Bob- If you have time, or when you have time later- maybe I can explain better. Give me a call.... just give me time to get my headset on...
Btw: I tried the code you gave me, Like "*?/?", but it only had one *, not two like your last post showed... Don't know if this is part of why I did not achieve the results I was hoping for. But I would like to talk to you about this.... Joseph gave me some good pointers that will be relevant.

[Bob Phillips]

That is exactly why. ? does a single character match so when you use

*?/? it will match any characters then any single character then a / , then any single character. So these match

123/1
a/b
1/c
a12/2
etc.

but these will not

123/12
1/12
a/12
a/a/b
etc.

So if you were using a format of ??/?? then 11/12 has two character after the / so it will fail that test

But if you use Like *?/?? a value of 7/8 will fail.

But you said you have a solution now, so you don't need to bother explaining it.

[YellowLabPro]

Rory-
Code runs fine in test book- breaks on first record in the Function in my real datasheet

strNumerator = String(Len(Trim$(Mid$(strText, intSpace + 1, intDivisor - intSpace - 1))), "?")

[YellowLabPro]

Rory,
It is happening on a cell w/ a whole number. This may be the cause.... But I am not clear enough on how to implement on error- especially w/ Functions.

[YellowLabPro]

Rory,
I got this to work: I changed this line in the code... Don't ask me how... but w/ Xld showing me some things and your fantastic code.... it worked

If .Value <> Int(.Value) And Len(.Value) > 0 Then

[YellowLabPro]

Rory,
My last solution has some glitches. If I need to do an about face, let me know.
The code is changing all my sizes to fractional form now, but there are some that need to maintain their decimal numbering.
This has been accomplished previously by an array in another module- I attempted to incorporate it, but getting lost how to add it in.

The additional condition that if the Item is in the array that is in either col. F or G then perform your code on these values.
The array is similar to the other I am using:

For i = 4 To LRowF
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY", "HWRISR")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then

Current code tinkering w/

Sub SetFormats()
Dim rngcell As Range
Dim item As Variant
Dim i As Integer

For i = 4 To 12000
For Each item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY", "HWRISR")

For Each rngcell In Range("M4:M12000") 'Selection
With rngcell
If .Value <> Int(.Value) And Len(.Value) > 0 Then
.NumberFormat = "# ??/??"
.NumberFormat = GetFractionFormat(.Text)
.Formula = .Value
Exit For
End If
Next item
End With
Next rngcell
End Sub

[YellowLabPro]

I have made a little progress, but need the help of VBA Geniuses here... Don't hold back- you know who you are and I really need the help.

The code is supposed to loop through col. M and correct Mixed Number sizes and Fractional Sizes.
It is supposed to be limited to correct only the sizes that are listed in the first array.
It is not being limited as it is editing all non-integer numbers. As I watch in the Locals window, I think it is failing to use the Array of values as criteria to perform its task.


Sub SetFormats()
Dim rngcell As Range
Dim Item As Variant
Dim i As Integer

For i = 4 To 12000
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY", "HWRISR")
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) Then
For Each rngcell In Range("M4:M12000") 'Selection
On Error Resume Next
With rngcell
If .Value <> Int(.Value) And Len(.Value) > 0 Then
.NumberFormat = "# ??/??"
.NumberFormat = GetFractionFormat(.Text)
.Formula = .Value
End If
End With
Next rngcell 'Exit For
On Error GoTo 0
End If
Next Item
'Exit For
Next i
End Sub

[Tommy]

I changed some stuff but fixed per your request and commented. :)

Sub SetFormats()
Dim rngcell As Range
Dim Item As Variant
Dim i As Integer

For i = 4 To 12000
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTW", "BOOTY", "HWRISR")
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) Then
'this will change all formating in the selection if a match to Item is found
'For Each rngcell In Range("M4:M12000") 'Selection
On Error Resume Next
'changed this to check the cell on the row that matches the array
With Cells(i, "M")
If .Value <> Int(.Value) And Len(.Value) > 0 Then
.NumberFormat = "# ??/??"
.NumberFormat = GetFractionFormat(.Text)
.Formula = .Value
End If
End With
'Next rngcell 'Exit For
On Error GoTo 0
End If
Next Item
'Exit For
Next i
End Sub

[YellowLabPro]

Rory/Tommy-
Never Mind- Big Fat Stupid Mistake on my part...

[rory]

I take it you spotted the loop within a loop again? ;)

[YellowLabPro]

Yes... but not because I am so clever
I was watching it in the Locals window and it was hung up in the first loop not changing the item value.
When you pointed it out initially I did not see the double loop, Xld showed me and then it made sense...
Like I said before, glad to have you on the board!
Ps. thanks for getting Ruth the code. I had finished 2/3rds but still had some work to do on it, glad you were able to help out.

cheers,

Doug

[YellowLabPro]

Rory,
Ps. I don't think I have properly thanked you for that piece of code. It really is a lovely piece of work- clever, concise, efficient- thank you.

Doug

[rory]

Doug,
Any time, my friend. I hope I didn't tread on your toes by posting that code for Ruth; I knew you were working on it but was also aware you had your own stuff on the go, so wanted to give her something to test at least in case you had not had time to finish.
Rory

[YellowLabPro]

Nope, no treading at all-- I learned a lot just doing what I did. I will compare both pieces once I get my new website up, should be by the end of the week- This has been a 2yr deal and I am more than ready to be done.