Help rewrite line of code to prevent changing numberformat

D

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format.
That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values.
I added the function Isnumeric to this line:
Code: 
Isnumeric(cells(i, "M").value) <> Int(cells(i, "M").value)
which I thought would only evaluate numeric cells only, but this was unsuccessful.

Original Code: Unedited.
Code: 
For i = 4 To LRowf
    For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
          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
          Cells(i, "M").NumberFormat = "# ?/?"
          On Error GoTo 0
      Exit For
          'End If
               End If
        
    Next Item
Next i
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hi,

The test Isnumeric(cells(i, "M").value) will return True or False. There is not need to compare it to Int(Cells(i, "M").Value)

Try If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value <> Int(Cells(i, "M").Value) And _
Isnumeric(cells(i, "M").value) Then
...


HIH
 
Upvote 0
I think I tried this and it did not work, but I will try again tomorrow.

In the mean time I wrote a second If
Code: 
If Not IsNumeric(Cells(i, "M").Value) And Cells(i, "M").NumberFormat = "# ?/?" Then
Cells(i, "M").NumberFormat = "General"

I will post back, thanks
 
Upvote 0
Re: Help rewrite line of code to prevent changing numberform

How about?
Code: 
Dim myArray, x, y
myArray = Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
For i = 4 To LRowf
      x = Application.Match(Cells(i, "F").Value, myArray,0) 
      y = Application.Match(Cells(i, "G").Value , myArray,0) 
      If (Not IsError(x) * x = y) * Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
          Cells(i, "M").NumberFormat = "# ?/?"
      End If
Next i
 
Upvote 0
Hello Kieran,
I was mistaken about testing w/ your version. I had not added the additional condition to test. That is good logic, thanks. But it still fails, and not for why I thought at all. It has nothing to do w/ the cell value in col. F or G.
It fails because of the Int Function and the On Error Resume Next.
In col M some of the values are text, the Int Function cannot evaluate it properly so it errors. Once it does that, the next line is instructing it to change the numberformat to "# ?/?", just as the code is telling it to do so.

Code: 
For i = 4 To LRowf 
    For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS") 
          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 
          Cells(i, "M").NumberFormat = "# ?/?" 
          On Error GoTo 0 
      Exit For 
          'End If 
               End If 
        
    Next Item 
Next i

So my thought in how to resolve this a littel more elegantly is to see if I can instruct the code not to resume to the next line, but resume the next i.

This is my current code that handles it, which is fine for practical methods, but would be nice to solve for the on error resume level-

Code: 
For i = 4 To LRowf
    For Each Item In Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "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
          Cells(i, "M").NumberFormat = "# ?/?"
          On Error GoTo 0
If Not IsNumeric(Cells(i, "M").Value) And Cells(i, "M").NumberFormat = "# ?/?" Then
Cells(i, "M").NumberFormat = "General"


      Exit For
          End If
              End If
        
    Next Item
Next i

cheers,
 
Upvote 0
Hello Jindon,
Thanks- you always have a slick way of handling this stuff: :)

Before I change my approach to yours', I would like to research/solve for the current code.
Note* After I resolve my current issue I will swap out my code for yours (I am anxious to start incorporating arrays into my work- I just have yet to really understand them on any real level. I just understand what they are.)

Any help on solving for the On Error Resume issue and I would be most grateful.
 
Upvote 0
I have added a line that I thought would be an answer to my issue,
Code: 
On Error GoTo Again
the logic seems ok to me, but my code is generating a Run-time 10 error.
The msg is "This array is fixed or temporarily locked.
Code: 
For Each Item In Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
          On Error GoTo Again '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
          Cells(i, "M").NumberFormat = "# ?/?"
          On Error GoTo 0
If Not IsNumeric(Cells(i, "M").Value) And Cells(i, "M").NumberFormat = "# ?/?" Then
Cells(i, "M").NumberFormat = "General"


      Exit For
          End If
              End If
        
    Next Item
Again:     Next i
 
Upvote 0
What I think is going on at the current moment is when an error is found, the code starts at the next i, but the array is in the middle w/ some value in memory, I need to re-initialize the array-
How would I re-initialzie the array to "Empty"?
 
Upvote 0
Re: Help rewrite line of code to prevent changing numberform

Code: 
Isnumeric(cells(i, "M").value) <> Int(cells(i, "M").value)
How about
Code: 
IsNumeric(cells(i, "M").value) * (cells(i, "M").value Mod 1 <> 0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
333
MARK858
MARK858
I
  • Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
188
ItalianPlatinum
I
I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
257
ItalianPlatinum
I
M
  • Solved
Changing the position of a message box
Replies
1
Views
291
6StringJazzer
6StringJazzer
K
  • Question
Change code from inside sheet to userform directly
Replies
0
Views
160
KalilMe
K

Forum statistics

Threads
1,214,925
Messages
6,122,303
Members
449,078
Latest member
nonnakkong

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top