ondrej.kanta
11-15-2012, 07:33 AM
Hi all,
I have written the following code. I'm sorry it's in czech, however, the only word you need to know is "ucet" which means "an account", so please do not pay attention to the 'comments and other stuff :) :
blah blah imo not important part of code
Do Until ucet = "" 'Vložení čísla účtu do proměnné
Application.ScreenUpdating = False
ucet = InputBox("Zadejte číslo účtu, který chcete vložit", "Zadání účtu", "XXXXXX")
Sheets("data").Select 'Hledání ve sloupci podle vloženého čísla účtu
Range("A1").Select
With Sheet1
Cells.Find(What:=ucet, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End With
'Označení oblasti ke kopírování
Range(ActiveCell, Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Find(What:="Opening Balance", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)).Select
Range(Selection, Cells(ActiveCell.Row, 40)).Select
Selection.Copy 'Kopírování
Sheets("vystup").Select
Range("A1048576").Select
Selection.End(xlUp).Offset(3, 0).Select
Application.ScreenUpdating = True
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Selection, Selection.End(xlDown)).Select 'Kvůli zobrazení
Application.Goto ActiveCell
Loop
blah blah imo not important part of code
I use it to rearrange accounting reports according to the needs of clients. Accounts are in A:A, user (me) enters acc number into inputbox, it finds the start of block of all items belonging to the entered acc, selects rows to "Opening balance" which is the end of block, copies to output sheet and repeats until cancel.
The problem appears when user enters an acc number which does not appear in A:A and the macro crashes.
Could you please help me fix it? What I was thinikg of doing was to show MsgBox("Acc does not exist, please retry.") and return to the beginning of the loop, but I'm just an advanced beginner in VBA (or coding at all).
Many thanks! :thumb
I have written the following code. I'm sorry it's in czech, however, the only word you need to know is "ucet" which means "an account", so please do not pay attention to the 'comments and other stuff :) :
blah blah imo not important part of code
Do Until ucet = "" 'Vložení čísla účtu do proměnné
Application.ScreenUpdating = False
ucet = InputBox("Zadejte číslo účtu, který chcete vložit", "Zadání účtu", "XXXXXX")
Sheets("data").Select 'Hledání ve sloupci podle vloženého čísla účtu
Range("A1").Select
With Sheet1
Cells.Find(What:=ucet, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End With
'Označení oblasti ke kopírování
Range(ActiveCell, Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Find(What:="Opening Balance", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)).Select
Range(Selection, Cells(ActiveCell.Row, 40)).Select
Selection.Copy 'Kopírování
Sheets("vystup").Select
Range("A1048576").Select
Selection.End(xlUp).Offset(3, 0).Select
Application.ScreenUpdating = True
ActiveSheet.Paste
Application.CutCopyMode = False
Range(Selection, Selection.End(xlDown)).Select 'Kvůli zobrazení
Application.Goto ActiveCell
Loop
blah blah imo not important part of code
I use it to rearrange accounting reports according to the needs of clients. Accounts are in A:A, user (me) enters acc number into inputbox, it finds the start of block of all items belonging to the entered acc, selects rows to "Opening balance" which is the end of block, copies to output sheet and repeats until cancel.
The problem appears when user enters an acc number which does not appear in A:A and the macro crashes.
Could you please help me fix it? What I was thinikg of doing was to show MsgBox("Acc does not exist, please retry.") and return to the beginning of the loop, but I'm just an advanced beginner in VBA (or coding at all).
Many thanks! :thumb