27. oktober 2016 - 14:19Der er
27 kommentarer og 1 løsning
Sammensmeltning af to makroer - Excel
Hej Eksperter!
Jeg har tidligere af en super dygtig bruger herinde fået lavet en super løsning i Excel. (avanceret makro)
Jeg vil nu gerne have justeret denne løsning.
Jeg har et Excel dokument med to makroer. Hver makro udføres ved klik på en knap og dette skal gøres i den rigtige rækkefølge.
Vores udfordring er at enkelt brugere glemmer at bruge knappen ?indlæs? prioriteter, hvilket gør at prioriteringsrækkefølgen er forkert. Andre brugere kommer fejlagtigt til at klikke to gange på knappen ?opdater? og så er prioriteterne også forkerte.
*) Den rigtige proces er at:
1. Klik på ?opdater? ? Nyt data hentes fra ERP system, og manuelt data indtastet overføres til arket Prioriteringsdata. 2. Klik på ?indlæs Prioriteter? ? De manuelle prioriteter der blev overført til arket Prioriteringsdata tilbageføres og sorteres i numerisk rækkefølge.
Nogen der har mod på at få tilsendt arket og prøve at lave makroen om såldes de begge kan køres ved et klik på en knap?
De to makroer skal reelt set blot sættes i enderne af hinanden. Hvis der er sammenfald mellem variable kan det kræve lidt mere, men lig evt. filen på gratisupload.dk eller via dropbox eller lignende så tror jeg hurtigt du har svaret.
Jeg er ikke vildt stærk til VBA, men jeg tror at de to makroer hænger sammen, og derfor skal afvikles på en helt bestemt måde for at det fungere. Jeg har prøvet at sammenskrive dem, men det fungere desværre ikke.
Jeg prøver at uploade min Excel fil og se om der er nogen der kan hjælpe!
Know almost nothing about Excel but I've looked at your Excel file and added a button which calls a new macro which then executes the other tow macros.
Not sure if it works or if its what you want. Give me an email and I'll send it
Løsningen med at lave en makro der kører begge makroer fungere ikke. Den har jeg selv prøvet. De to forskellige makroer har variabler begge bruger, hvorfor de skal sammenskrives på en måde.
Thank you for your attempt. But it doesnt work. The second macro is supposed to handle the priorities and to set them right as they were before the update, and the priorities goes in the wrong lines when using your macro.
Thing is that I import data from our ERP system which contains jobs for our production operators. These jobs I want to manually prioritize in my spreadsheet. This I do with the column "Prio". Here I punch in numbers manually from 1 to 99. Later i will update with new jobs that are new in the ERP database, meaning new lines will show up, and old lines will disapear in the spreadsheet. I want to combine the ERP data with my manual data when I update. so The first macro takes a copy of my manual punched in "Prio" and then updates with Data from ERP system. When this is done the prio numbers are in the wrong cells as new data has moved them up and down. Therefore I run the second macro wich puts the prio's in the right places as they were before the update. Then the difficult thing comes up: Sometimes I have a filter e.g. I only look at one machine or one operators jobs. This means that there are hidden lines, and this really messes with getting the prio's in the right cells, therefore there was made changed to both macros to work eventhough there was a filter.
The change with "taking care of the filter" is what I think makes the two macros have variables both macros uses. BUT - I am no expert, and I am not sure i am right!
But the file you maked, didnt put the prio's in the right places :-)
Silly question, but does it work with using buttons in right order, also when you only look at one machine or one operator?
How do you get the data from ERP system into Excel?
If there are no manual operations between clicking the two buttons then the only thing I can think of which can be different is that the spreadsheet hasnt been fully updated between the two macro's being execute, if you see what I mean? So maybe some update event needs placing between macros, not expert at Excel :-(
Private Sub CommandButton1_Click() GemPrioOpdateringHentPrio End Sub
Private Sub CommandButton2_Click() OpdaterFilterPrio End Sub
Og tilføj kode i Module1:
With ActiveWorkbook.Worksheets("Jobliste").ListObjects( _ "Tabel_Forespørgsel_fra_AAOEDW").Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With
ActiveWorkbook.Worksheets("Jobliste").CommandButton1.Enabled = True ActiveWorkbook.Worksheets("Jobliste").CommandButton2.Enabled = False End Sub Rem zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Public Sub GemPrioOpdateringHentPrio() houseKeeping førOpdatering
ActiveWorkbook.RefreshAll
efterOpdatering Afslutning
ActiveWorkbook.Worksheets("Jobliste").CommandButton2.Enabled = True ActiveWorkbook.Worksheets("Jobliste").CommandButton1.Enabled = False End Sub
If there are no manual processes between pressing each of the buttons (in correct order) then its logical to have only one button. No idea why its not working with one although as I mentioned in my previous comment, I think it has something to do with updates not being saved before running new code.
That's a pity. Without having access to data so its possible to test then its not easy to find a solution. If you have a way to do that then that could help.
I'm no expert at Excel so dont really want to start digging into code I know little about, although I still dont understand why running code from both macros doesn't work. :-(
Jeg fandt ud af at problemet lå i at mine makroer ikke fungerede fordi hentningen af data fra ekstern kilde ikke var afviklet før næste makro blev sat i gang. Jeg løste det ved at deaktivere baggrundskørsel. Så ventede anden makro på at hentningen fra ekstern kilde var gennemført.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.