Can't fix On Error statement

A

austris

I've got a code that rearanges a table in the order I need. I'm using a
code and not just sort because values in the row 1 change.

The idea is something like this - i've got a table with values:
row 1: A B C D
row 2:
row 3:
row 4:
row 5: C B D
row 6: 1 2 3
row 7: 1 2 3

And i need the code to rearange it like this:
row 1: A B C D
row 2: B C D
row 3: 2 1 3
row 4: 2 1 3
row 5:
row 6:
row 7:

The problem is that as the value "A" doesn't exist in row 5, the search
returns error and i want the code then just pick the next value in that
row 1 (that would be B) and search for it in row 5, but it doesn't
(Run-time error 91). I guess i need to modify the On Error statement
but can't figure out how...

That's the code:

Sub Rearange_report()

lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
firstrow = Range("A1").End(xlDown).Row
lastcolumn = Range("A1").End(xlToRight).Column

For v = 1 To lastcolumn Step 1
Rows(firstrow).Select 'select first data (entire) row
Cells(firstrow, 256).Activate

On Error GoTo a
Selection.Find(What:=Cells(1, v).Value, After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Select

Range(Cells(firstrow, Selection.Column), Cells(lastrow,
Selection.Column)).Select
Selection.Cut
Cells(2, v).Select
ActiveSheet.Paste

a:
Next v

End Sub


Any adivse?
Thanks,

A.
 
S

Stefi

Try this (not tested):
Sub Rearange_report()

lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
firstrow = Range("A1").End(xlDown).Row
lastcolumn = Range("A1").End(xlToRight).Column

For v = 1 To lastcolumn Step 1
Rows(firstrow).Select 'select first data (entire) row
Cells(firstrow, 256).Activate
On Error Resume Next
Selection.Find(What:=Cells(1, v).Value, After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Select
On Error GoTo 0
If Err.Number = 0 Then
Range(Cells(firstrow, Selection.Column), Cells(lastrow,
Selection.Column)).Select
Selection.Cut
Cells(2, v).Select
ActiveSheet.Paste
End If
On Error GoTo 0
Next v

End Sub

Regards,
Stefi


„austris†ezt írta:
 
A

austris

Thanks, Stefi

The Error doesn't fire anymore but it still doesn't pick the next value
- it proceeds with copying values from first column, i.e., in the
example it copies C11 under A while it should copy under C...

Any ideas?
 
S

Stefi

Now it's tested! (I forgot to delete On Error GoTo 0 at the wrong place.)


Sub Rearange_report()
lastrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
firstrow = Range("A1").End(xlDown).Row
lastcolumn = Range("A1").End(xlToRight).Column
For v = 1 To lastcolumn Step 1
Rows(firstrow).Select 'select first data (entire) row
Cells(firstrow, 256).Activate
On Error Resume Next
Selection.Find(What:=Cells(1, v).Value, After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Select
If Err.Number = 0 Then
Range(Cells(firstrow, Selection.Column), Cells(lastrow,
Selection.Column)).Select
Selection.Cut
Cells(2, v).Select
ActiveSheet.Paste
End If
On Error GoTo 0
Next v
End Sub

Regards,
Stefi

„austris†ezt írta:
 
A

austris

Stefi,
Great! Now it works! Thanks A LOT!!!
Still, if it's not too much asked... - I'm trying to understand why it
didn't work but now does, so that i could fix similar issues next time
myself.
So, if you could let me know if this would be correct description:
1. this bit "On Error Resume Next" makes the code jump to next
statement in the code, i.e., "If Err.Number = 0"
2. the "Err.Number = 0" basically means that there is no Error, i.e, if
there is an error, then "If Err.Number = 0" returns FALSE and that
trigers the "On Error GoTo 0"
3. the "On Error GoTo 0" in turn stops any Error handler and thus the
code just proceeds to next statement, i.e. "Next v"
and that's it.

It would be immensely appreciated if you coould confirm/comment if the
above is correct - i wouldn't trouble you next time :)

And thanks again!
 
S

Stefi

So, if you could let me know if this would be correct description:
1. this bit "On Error Resume Next" makes the code jump to next
statement in the code, i.e., "If Err.Number = 0"
Click to expand...

Yes, if you placed "If Err.Number = 0" statement after the statement causing
the error.
2. the "Err.Number = 0" basically means that there is no Error, i.e, if
there is an error, then "If Err.Number = 0" returns FALSE.
Click to expand...

Yes, but it doesn't trigger anything! It only gives you the opportunity to
create branches for normal case and error case. The "On Error GoTo 0" only
stops any Error handling.
3. the "On Error GoTo 0" in turn stops any Error handler and thus the
code just proceeds to next statement, i.e. "Next v"
Click to expand...

Yes, AND it sets back Err.Number to 0!!! That was the mistake in my first
(wrong) post.

Have a look at On Error in XL Help, there is another facility there (On
Error GoTo line) for more complicated cases.

Regards,
Stefi
 
A

austris

Excellent! Thanks for the help and patience!
Yes, if you placed "If Err.Number = 0" statement after the statement causing
the error.


Yes, but it doesn't trigger anything! It only gives you the opportunity to
create branches for normal case and error case. The "On Error GoTo 0" only
stops any Error handling.


Yes, AND it sets back Err.Number to 0!!! That was the mistake in my first
(wrong) post.

Have a look at On Error in XL Help, there is another facility there (On
Error GoTo line) for more complicated cases.

Regards,
Stefi
Click to expand...
 
Top