On Error GoTo Again: Erase Array

[YellowLabPro]

I was looking to solve this issue different initially. Currently I am trying the approach that when an error occurs to skip the error, Erase my array to re-initialize it and resume w/ the next cell in the loop.
I am cross-posting this here from MrExcel-
http://www.mrexcel.com/board2/viewtopic.php?t=286219

If this is a "valid" approach, I am unable to get the sequence of instructions in the correct order. I can get to the Again: or I can get to the next i, but I am unable to Erase the array.
If it is invalid- any advice? I can revert to my original code and run the second If condition, If Not IsNumeric. But I prefer to find a cleaner method than that.


For i = 4 To LRowf
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
Next i
Again: Erase Item

[Norie]

Why don't you actually create an array that can then be erased.

arrVals = Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
For Each Item In arrVals

Also a few points.

1 Try and avoiud using Goto.

2 Avoid using variable names like Item, that's a property of various objects in VBA.

3 Why do you need to erase the array anyway?

4 You might want to look into Select Case.

[YellowLabPro]

Hello Norie,
Thanks for the help. I have some questions pretaining to your points.
1. Why can't my array be erased as it stands right now?

2. Do I just treat arrVals as a variable, and I do not need to set arrVals as an object?

3. How/where in your code do I erase my array and re-establish it; how do I do this w/out a GoTo line?

4. "Why do I need to erase the array"?
What appears to me to be an accurate observation of what is causing the error is that; when the following line of the If condition comes across a text string in the cell it is evaluating, rather than numeric value, it errors. My original code then instructed to resume next which is to make the numberformat for current cell a fraction format- which I do not want.
And Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then

What I thought would be a way of handling this was upon error to goto the next i, and continue the loop. However, the loop is inititiated and becomes fixed or locked, does not get reset and my approach fails.

5. Not disagreeing w/ your opinion about Select Case, but I feel there could/should be a way of handling this w/ my current code w/out totalling abandoning my work at this point. I have working code w/ a second If condition that looks at the cell and if the number is not numeric and has a fractional number format, it changes it back to General. I just find this to be a poor approach.... treating the symptom not the problem manner of writing the code.

I will try implementing your suggestion, though it is unclear. Please post back your thoughts/suggestions regarding my follow-up questions.

[YellowLabPro]

Norie,
In my first approach w/ your suggestion of arrVals, it gets through the first loop of all Items', starts on the next i and I get a error that the loop cannot be initialized-
I must have something incorrect in the code:


For i = 4 To LRowf
' For Each Item In Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
For Each Item In arrVals
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: Erase arrVals
Next i

[YellowLabPro]

By swapping the last two lines in my last post,

Again: Erase arrVals
Next i

for

Next i
Again: Erase arrVals

I now am able to get the code to run until it loops through and finds the first instance of a non-numeric value. The code then behaves as it did previously and breaks w/ an error that the array is either fixed or locked.

The code breaks on i 17 (row 17) where the value is text. In the Locals window, the first value is Empty of the array, it finds the text value in i,"M" which is an error then jumps to Again: Erase arrVals, this is actually where the breaks; i.e. Run-time error 10 (the array is fixed or temp. locked). It does not go to the next i.

[Norie]

Doug

1 The array doesn't really exist in the first place.

2 arrVals is an array of variables, not really an object.

3/4 I still don't actually understand why you want/need to erase the array. Do you actually understand what erasing an array does?

5 Not much code to replace.:)

By the way obviously posting code is a good thing, but it can sometimes also help if you explain in plain English it's purpose.

We can make certain assumptions by reading the code but not knowing what it's actual purpose is and how it's being used could mean those assumptions are wrong.

And if that's the case any answer/advice we give could be wrong too.

[YellowLabPro]

Thanks Norie,

3/4. "Do I understand what erasing the array does?"- from what I have read it appears to me that it is setting the values back to empty "". Which is what I want, I believe.
"Why do I want to erase it?" Because once the following line of code executes-

And Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
on a text string, it errors since it is not an integer, (so I believe).
I have evaluated each line as it runs through, this is where it breaks, and breaks on non-numeric values, only. This is how I drew my conclusion.)

Explanation of code's purpose(this block) per your request.
With my original code, it exits the code on error and leaves the array in mid-stream on the first term when it breaks and so I need to reinitialize it. The array is comparing values to what is in col. F or G, if it matches and the term is numeric it takes action, if it does not match any of the values in the array it goes to the next item, and the next until it cycles through the array and goes the the next i.

With my updated code subbing in your approach making the array a variable, it now appears to re-initialize the array, but still does not know where to go once it goes to Again: The loop is not initialized.

5. I did not want to convilute the post: but here is the entire procedure. It is not the little bit that I am concerned w/ replacing, it is the total completion of my idea in solving, also not following through on a problem. As it will most likely pop-up again and I will not have resolved it, rendering me unknowing still.....
The first part is commented out and would be subbed in where the current working code exists once it is solved for. I agree w/ you, not that much code.

''''Testing Code to On Error Erase Array
' For Each Item In Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
' For Each vItem In arrVals
' On Error GoTo Again: 'Resume Next
'If Cells(i, "F").Value = vItem Or Cells(i, "G").Value = vItem _
'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 vItem
'10: Next i
'Again: Erase arrVals: GoTo 10:

Sub Dec2Frac()
Dim Wsf As Worksheet, Wsv As Worksheet
Dim i As Integer, ii As Integer
Dim LRowf As Long, LRowV As Long
Dim vItem As Variant, Num As Variant
Dim FF As String, VB As String
Dim c As Range
Dim arrVals As Variant
FF = "PCCombined_FF"
VB = "PCCombined_VB"
arrVals = Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
Set Wsf = Worksheets(FF)
Set Wsv = Worksheets(VB)
LRowf = Wsf.Cells(Wsf.Rows.Count, 1).End(xlUp).Row
LRowV = Wsv.Cells(Wsv.Rows.Count, 1).End(xlUp).Row

With Wsf
.Range("M4:M" & LRowf).Formula = .Range("M4:M" & LRowf).Value
.Columns("M").Calculate
End With
With Wsv
.Range("M4:M" & LRowV).Formula = .Range("M4:M" & LRowV).Value
.Columns("M").Calculate
End With
For i = 4 To LRowf
For Each vItem In Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
On Error Resume Next
If Cells(i, "F").Value = vItem Or Cells(i, "G").Value = vItem _
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 vItem
'---------------------------------------------VB---------------------------------------------------------
For ii = 4 To LRowV
For Each vItem In Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
On Error Resume Next
With Wsv
If (.Cells(ii, "F").Value = vItem Or .Cells(ii, "G").Value = vItem) And _
.Cells(ii, "M").Value <> Int(.Cells(ii, "M").Value) Then
.Cells(ii, "M").NumberFormat = "# ?/?"
On Error GoTo 0
If Not IsNumeric(.Cells(ii, "M").Value) And .Cells(ii, "M").NumberFormat = "# ?/?" Then
.Cells(ii, "M").NumberFormat = "General"
Exit For
'End If
End If
End If
End With
Next vItem
Next ii
[D4].Select
End Sub

[Norie]

Doug

I'm sorry but your losing me here.:eek:

Why would you want to change the values in the array to ""?

Are you going to use the array for something else?

Are you going to repopulate it with different values?

As to the explanation, that's not what I meant really - most of that can be interpreted from the code.

What I meant was something like this:

The code moves down a column checking for particular values. eg the values in the array.

Then dependent on that it takes some action. eg does a litle formatting

[YellowLabPro]

Norie,
No problem, Don't mean to lose or confuse...

1. Why would you want to change the values in the array to ""?
From what I am seeing and from there concluding- that when the loop gets to a cell w/ a non numeric value, it breaks.
To solve for that so the loop will continue through the entire range, I wanted to trap the error and have the loop start over on the next i.
But in doing so, the array is left stranded. It has been initialized and when the loop moves to the next cell, the next i, the For Each loop is fixed or locked, so my thought was to erase the loop, or reinitialize the loop in the array. This was the method I found searching and it seemed to do the task I needed.

2. No I do not want to use different values, but rather the same that are in the original array.

The explanation- ok... just did not want to be ambiguous- I know it is hard to interpret what someone else is trying to explain.

Your description of my explanation is accurate.... :-)

Does this help sort it out?

[Norie]

Doug

It helps a little.:)

But I really think you need to have a bit of a rethink.

Here's a couple of more points:

1 In your code the array doesn't really exist in the first place, so how can you erase something that doesn't exist.

2 Why erase? That empties the array, ie it removes all the items from it. So how can you go on and use the values that were in the array previously, they aren't there anymore.

3 I would suggest using For LBound(arrVals) To UBound(arrVals) rather than For Each.

[rory]

Doug,
If I might add my tuppenceworth to the discussion:
1. I agree that you do not need to erase the array. It's a constant array, so erasing it serves no purpose whatsoever.
2. This is not, IMHO, a good use of error handling. You know that the error occurs when you have a non-numeric value (Not surprising that the Int function chokes on text!), so you should really be adding a test to check if the value is numeric. (In actual fact, you do have such a test but it is placed after the point where your error occurs!) Then no error will be thrown and your code can proceed normally.
Regards,
Rory

[YellowLabPro]

Hello Rory,
Thanks for the tuppenceworth- The reason I used the erase method is inexperience. I ended up having to handle one result created by another- (the known error created the 2nd situation).
How would you handle this? I have read through that block of code and do not see it.*
I do see it, just not sure if it is where you are suggesting to handle it.

And Cells(i, "M").Value <> Int(Cells(i, "M").Value) Then
would become
And IsNumeric(Cells(i, "M").Value) <> Int(Cells(i, "M").Value) Then

[rory]

Something like:
Sub Dec2Frac()
Dim Wsf As Worksheet, Wsv As Worksheet
Dim i As Integer, ii As Integer
Dim LRowf As Long, LRowV As Long
Dim vItem As Variant, Num As Variant
Dim FF As String, VB As String
Dim c As Range
Dim arrVals As Variant
FF = "PCCombined_FF"
VB = "PCCombined_VB"
arrVals = Array("BOOT", "BOOTG", "BOOTY", "FTWR", "HAT", "HWBLTS", "HWRISR")
Set Wsf = Worksheets(FF)
Set Wsv = Worksheets(VB)
LRowf = Wsf.Cells(Wsf.Rows.Count, 1).End(xlUp).Row
LRowV = Wsv.Cells(Wsv.Rows.Count, 1).End(xlUp).Row

With Wsf
.Range("M4:M" & LRowf).Formula = .Range("M4:M" & LRowf).Value
.Columns("M").Calculate
End With
With Wsv
.Range("M4:M" & LRowV).Formula = .Range("M4:M" & LRowV).Value
.Columns("M").Calculate
End With
With Wsf
For i = 4 To LRowf
If (InArray(arrVals, .Cells(i, "F").Value) Or InArray(arrVals, .Cells(i, "G").Value)) _
And IsNumeric(.Cells(i, "M").Value) Then
With .Cells(i, "M")
If .Value <> Int(.Value) Then .NumberFormat = "# ?/?"
End With
End If
Next i
End With
'---------------------------------------------VB---------------------------------------------------------
With Wsv
For i = 4 To LRowV
If (InArray(arrVals, .Cells(i, "F").Value) Or InArray(arrVals, .Cells(i, "G").Value)) _
And IsNumeric(.Cells(i, "M").Value) Then
With .Cells(i, "M")
If .Value <> Int(.Value) Then .NumberFormat = "# ?/?"
End With
End If
Next i
End With
[D4].Select
End Sub
Function InArray(arrData As Variant, varVal As Variant) As Boolean
InArray = Not IsError(Application.Match(varVal, arrData, 0))
End Function


I didn't really see the need for the code to set the numberformat to General if the value is not numeric - if it's not numeric, a number format will have no effect anyway.

Regards,
Rory

[YellowLabPro]

Good Morning Rory,
Thanks for the huge re-write. I am in the middle of solving for another issue at the moment- I was able to get this to work last night in the old code, so I am going to use it throughout today so I can keep making progress. I was not expecting a change of this nature so I am holding off until I can get some other smaller details worked out.... better to deal w/ the demons I know, rather than the ones I don't.
I will come back to it tonight and replace and test.
Thanks for taking the time to do such a major re-write.

Doug

[rory]

No worries - I didn't change that much. Note that I've just edited the code as I missed an End With statement in there.
Rory