Can't fix On Error statement
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.
|