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.

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:

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.

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?

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:

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?

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!

Stefi wrote:
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:

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?

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"

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.

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"

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

Excellent! Thanks for the help and patience!

Stefi wrote:
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"

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.

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"

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

You are welcome! Thanks for the feedback!
Stefi

€žaustris€� ezt Ã*rta:

Excellent! Thanks for the help and patience!

Stefi wrote:
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"

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.

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"

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