Macro 1, Macro 2, Macro 3 > after each other! Need HELP!

N

NetZorro

New Member
Joined
Aug 23, 2005
Messages
27
Would REALLY need som help.
Today i use 3 macro.
I want to combine them.. i have search and read the form for so long now, and cant find an answare. Please help me!! Need this badly!

Macro 1

Code: 
Sub Search_and_Add_To_LagerLista_()
'
' Makro1 Makro
' Söker o kopiera in värden efter EAN-coder som redan finns i bladet "LagerLista"
'
' Kortkommando: Ctrl+r
'
'Del1: Bla. så börja dem med att placera sig längst upp, i det övre fönstret, och markera även
'kolumn E.  Efter det så letar den efter ett värde från C4, i hela Kolumn E
   With Sheets("LagerLista")
    Sheets("LagerLista").Select
    ActiveWindow.Panes(1).Activate
    Columns("E:E").Select
   .Columns("E:E").Find( _
            What:=Sheets("EAN_Inlasning").Range("C4").Value, _
            After:=ActiveCell, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False).Select
  End With
  
  'START av addering utav Antal
    ActiveCell.Offset(0, 3).Range("A1").Select
    Sheets("EAN_Inlasning").Select 'Förflyttas till EAN_Inlasning
    Range("B4").Select 'B4 dvs. Antal markeras
    Application.CutCopyMode = False
    Selection.Copy 'B4 dvs. Antal kopieras
    Sheets("LagerLista").Select 'Förflyttas till LagerLista
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False 'Värdet ifrån B4 adderas med det antalet längst till höger
    '****
 
Sheets("LagerLista").Select   'LevListor markeras (kan ev. ta bort) och kolumn E Markeras (för säkerhets skull, behövdes innan jag lade variablen högre upp
Application.CutCopyMode = False
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("EAN_Inlasning").Select   'Bladet EAN_Inlasning markeras
Range("C4").Select  'Nu är allt klart, och markören står på C4, och redo för att ändras!
End Sub

Macro 2

Code: 
Sub CopyEAN_SearchLevListor_PasteLagerLista()

'
' CopyEAN_SearchLevListor_PasteLagerLista Maro
' Makrot inspelat 2005-08-14
'
' Kortkommando: Ctrl+q
'

'Nedre 4 rader bara ser till att markören står på rätt sätt i LagerLista-bladet, eftersom det är viktigt.
Sheets("LagerLista").Select   'Bladet LagerLista markeras
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

'Går tillbaka till bladet EAN_Inlasning, där sökvärdet finns.
Sheets("EAN_Inlasning").Select
Range("C4").Select

'Del2: Bla. så börja dem med att placera sig längst upp, i det övre fönstret, och markera även
'kolumn E.  Efter det så letar den efter ett värde från C4, i hela Kolumn E
   With Sheets("LevListor")
    Sheets("LevListor").Select
    ActiveWindow.Panes(1).Activate
    Columns("E:E").Select
   .Columns("E:E").Find( _
            What:=Sheets("EAN_Inlasning").Range("C4").Value, _
            After:=ActiveCell, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False).Select
   End With
   
Selection.EntireRow.Select  'Här markeras den rad som hittar en "Match"
Selection.Copy  'Raden som var en "Match" kopieras
Sheets("LagerLista").Select     'Bladet LagerLista markeras
ActiveSheet.Paste      'Det som kopierades från Kolumn E, klistas in.
Selection.End(xlToRight).Select 'Markör flyttas så mycket det går till höger
ActiveCell.Offset(0, 1).Range("A1").Select 'Markör flyttas yttligare ett steg, Till Antal kolumnen
ActiveCell.FormulaR1C1 = "1" 'Antal får "grundvärdet" 1
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select   'Markören har nu flyttats ner så att nästa inklistring inte kommer ovanpå, utan under
Application.CutCopyMode = False
Sheets("EAN_Inlasning").Select   'Bladet EAN_Inlasning markeras
Range("C4").Select  'Nu är allt klart, och markören står på B2, och redo för att ändras!
    Sheets("EAN_Inlasning").Select
    Range("C4").Select
    Selection.Copy
    Sheets("LagerLista").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveSheet.Paste '**** av EAN-kopiering
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        Sheets("EAN_Inlasning").Select
        Range("B4").Select
    Selection.Copy
    Sheets("LagerLista").Select
        ActiveSheet.Paste '**** av Antal-kopiering
    Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

Macro 2

Code: 
Sub CopyNewToEAN_LagerLista()
'
' CopyNewToEAN_LagerLista Makro
' Makrot inspelat 2005-08-25 av Daniel Persson
'
' Kortkommando: Ctrl+t
'
    Sheets("EAN_Inlasning").Select
    Range("C4").Select
    Selection.Copy
    Sheets("LagerLista").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveSheet.Paste '**** av EAN-kopiering
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveCell.FormulaR1C1 = "xxxx"
        ActiveCell.Offset(0, 1).Range("A1").Select
        Sheets("EAN_Inlasning").Select
        Range("B4").Select
    Selection.Copy
    Sheets("LagerLista").Select
        ActiveSheet.Paste '**** av Antal-kopiering
    Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

Pleeessse...... bare with me!
/NetZorro
 
Sort by date Sort by votes
It works like This.
In Sheet 1 > EAN_Inlasning Here i have a barcodescanner, (an inputvalue)
In Sheet 2 > LevListor Here are my resellers EAN-code lists
In Sheet 3 > LagerLista This is were my "inventory-list" is being made.

So, If scan a Bar-code ex: 5566778899123 , i have today the 1 Macro (ctrl+r) to search Sheet 3 (LagerLista) to look, if i already have enterd the value once. If i have, i atomaticly adds a value i have presetted in Sheet 1 to be added to the match in Sheet 3.

If ther is no match using Macro 1, i before got en error message.

Then i used the 2 macro, to make the exact same search, but this time in Sheet 2 (LevListor) and if the EAN is a match in that list (withc it properbly is) it copy the hole row in Sheet 2 (were the match was) to Sheet 3 (my Inventory list.

If its still not a match in Sheet 2 (an error used to came up) and then i hade the 3 macro, to copy the EAN, straight to Sheet 3 (my inventory list)

As anyone of you understand, this feels like a real waste of time, and should be able to do more smoothe.

Like, if match in Macro 1, dont start Macro 2, and 3, if there is not an match in Macro 1, start Macro 2, (and skip Macro 3) and if there is not an match in macro 2, start macro 3.
Click to expand...

I think this is like a pease of cake for the most of you guys.. but for me.. it just drives me nuuts....

Help is more than welcome

Hope this explains my goals....

/NetZorro
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
TheNoocH

see if this works...

in module 2 put at the top

ON ERROR EXIT SUB

not sure if it'll work though b/c it may only exit macro2 not the consolidated macro....
Click to expand...

Noop.. it didnt work.. it says, it hade to be GoTo or Resume

The Error, is not the problem right now i think.. i think it is, that even if it is a match, the next thing happens, which it shouldn't

Give me something new, and i try... I realy need this to work...
/NetZorro
 
Upvote 0
NetZorro said:
It works like This.
In Sheet 1 > EAN_Inlasning Here i have a barcodescanner, (an inputvalue)
In Sheet 2 > LevListor Here are my resellers EAN-code lists
In Sheet 3 > LagerLista This is were my "inventory-list" is being made.

So, If scan a Bar-code ex: 5566778899123 , i have today the 1 Macro (ctrl+r) to search Sheet 3 (LagerLista) to look, if i already have enterd the value once. If i have, i atomaticly adds a value i have presetted in Sheet 1 to be added to the match in Sheet 3.

If ther is no match using Macro 1, i before got en error message.

Then i used the 2 macro, to make the exact same search, but this time in Sheet 2 (LevListor) and if the EAN is a match in that list (withc it properbly is) it copy the hole row in Sheet 2 (were the match was) to Sheet 3 (my Inventory list.

If its still not a match in Sheet 2 (an error used to came up) and then i hade the 3 macro, to copy the EAN, straight to Sheet 3 (my inventory list)

As anyone of you understand, this feels like a real waste of time, and should be able to do more smoothe.

Like, if match in Macro 1, dont start Macro 2, and 3, if there is not an match in Macro 1, start Macro 2, (and skip Macro 3) and if there is not an match in macro 2, start macro 3.
Click to expand...

I think this is like a pease of cake for the most of you guys.. but for me.. it just drives me nuuts....

Help is more than welcome

Hope this explains my goals....

/NetZorro
Click to expand...

not sure if this would be the best approach (maybe Norie has some insight) but you could try...

rather than the initial code i posted (before I understood what you're really after)...maybe something like this...
_______________________
***In Macro 1***
At the top of the code
On Error Goto SecondMacro

at the bottom of the code (right before end sub)

SecondMacro:
CopyEAN_SearchLevListor_PasteLagerLista

_______________________

***In Macro 2***
At the top of the code
On Error Goto ThirdMacro

at the bottom of the code (right before end sub)

ThirdMacro:
CopyNewToEAN_LagerLista

________________________


so you will always start by running macro one.....and the added code will be used to assign what to run next if anything....

note: untested but i think the logic is close if i'm following what you are after
 
Upvote 0
Now it seems like it runns Macro 1 twice and then Macro 3 once.
and thats not good... :-D


Does it exist something like
"On no Error dont resume and On Error resume next"

I think thats what you were trying to do.. but it doesnt work.
 
Upvote 0
NetZorro said:
Now it seems like it runns Macro 1 twice and then Macro 3 once.
and thats not good... :-D


Does it exist something like
"On no Error dont resume and On Error resume next"

I think thats what you were trying to do.. but it doesnt work.
Click to expand...

are you running the consolidated macro? or just running macro1?

you should get rid of the initial response related to the consolidated macro and just run the first macro with the above changes....
 
Upvote 0
Why not just combine all the code into 1 sub?
 
Upvote 0
hmm... yes and no.. i was using the old one.. but i changed it now.. and now it is not 3lines being copyed.. only two :-)

No it seems like this: If there is no match in LagerLista, it looks in LevListor, and if there is a match, i copys the hole row, to LagerLista.
But, after that, it still inisiatates the Macro 3 , meaning that it copy the EAN from EAN_Inlasning (sheet 1) to a new row in LagerLista.

then.. if i push the first macro once again (it should now, only start macro 1, seeing that there is a match in LagerLista, and it does that, but adds the number fault.. )
 
Upvote 0
Norie said:
Why not just combine all the code into 1 sub?
Click to expand...

I love to.. but i have tried so many times.. reading proberbly all the post on this forum.. not making it work....

Im not that advanced with macro yet.... so i just cant make it work like one subrutine. (1 macro, i gues u mean)

Any ideas..... im running out of theme...

/NetZorro
 
Upvote 0
TheNoocH said:
NetZorro said:
I think thats what you were trying to do.. but it doesnt work.
Click to expand...

are you running the consolidated macro? or just running macro1?

you should get rid of the initial response related to the consolidated macro and just run the first macro with the above changes....
Click to expand...

It seems like the macros "think there is a problem" and continue to the second Macro (or Third). But there is no visual fault, meaning some popup sign o whatever.

I seem to be soooo close but so far away.... all three macro works seperated, but all i whant is to for theme to work together...

Help.. please
 
Upvote 0
NetZorro said:
I seem to be soooo close but so far away.... all three macro works seperated, but all i whant is to for theme to work together...

Help.. please
Click to expand...

if you were going to do it manually (seperated) how would you do it...

eg
Run Macro1
If returns an error then i stop it and run Macro2
if it doesn't return an error then i'm done


If Macro2 returns an error i stop it and run Macro 3
if it doesn't return an error then i'm done...

somethign like that?
also how do the errors show...as halts to the code?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Years together I was using a macro for automate a w/book is not as on today why?
Replies
1
Views
475
GlennUK
G
Z
  • Solved
Apply 1 macro to all sheets in a workbook: Why is this code not working?
Replies
2
Views
455
ZA_Fella
Z
J
  • Question Question
VBA - need assistance with macro regarding one value vs multiple
Replies
1
Views
566
rlv01
R
A
  • Solved
Sumif not working on my macros but working when i am pressing F8
Replies
3
Views
579
Zot
Z
C
  • Solved
Can someone help me insert a loop into this macro I created?
Replies
9
Views
659
Alex Blakenburg
A

Forum statistics

Threads
1,223,941
Messages
6,175,541
Members
452,652
Latest member
eduedu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top