Den stora tråden om excel - Sidan 4 - Kolozzeum Forum

Jockefoten · 2015-09-19, 10:39

Någon av er som vet hur man sätter en range så den aktiverar alla "aktiva" celler?

Ju nu har jag ett makro som färgar tomma celler och jag vill kunna applicera makrot på ett ständigt ökande dokument med data.

Så här ser det ut idag, men eftersom A-AB ständigt ökar med data så behöver jag ju en annan "range".

Kod:

Sub Set_Color()
    Dim myRange As Range
    Set myRange = Blad1.Range("A2:AB9999")

    'clear all color
    myRange.Interior.ColorIndex = xlNone

    'color only blank cells
    myRange.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
End Sub

Jag antar att jag ska få in "end.(xldown).select" eller något liknande i range-koden men jag lyckas inte.

martikainen · 2015-09-20, 11:28

Lyckades få till del 1 av min lösning med att populera formler utan att ange rangen

Började med att ange värdet i A2 till FQDN då denna alltid kommer ligga där.
Sedan var det bara att använda sökfunktionen, och offset funktionen, dessa tillsammans söker efter den tidigare tänkte kolumnen och populerar sen sin formel efter denna.

Citat:

Rows("1:7").Select
Selection.ClearContents

Range("A2").Formula = "FQDN"

'this part checks for FQDN and jump one column to the right, inserts new value and jump down one row

Rows("2:2").Select
Selection.Find(What:="FQDN").Select
ActiveCell.Offset(0, 1).Select

' Short Hostname

ActiveCell.Formula = "Short Hostname"

Rows("2:2").Select
Selection.Find(What:="Short Hostname").Select
ActiveCell.Offset(0, 1).Select

' Virtual/physical

ActiveCell.Formula = "Virtual/Physical"

Rows("2:2").Select
Selection.Find(What:="Virtual/Physical").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=IFERROR(IFNA(VLOOKUP(""*""&serverlistFQDN&""*"", CMDB,2,0),(VLOOKUP(""*""&serverlistShortname&""*"" ,CMDB,2,0))),""Not found"")"
ActiveCell.Offset(-1, 1).Select

Nästa problem är nu att kunna köra "Define Name" på kolumner, också det genom Find funktionen eftersom jag inte vill behöva ange en exakt range.

Får gnugga vidare lite på internet och se om jag lyckas komma på det.

martikainen · 2015-09-22, 08:19

Citat:

Ursprungligen postat av martikainen

Lyckades få till del 1 av min lösning med att populera formler utan att ange rangen

Började med att ange värdet i A2 till FQDN då denna alltid kommer ligga där.
Sedan var det bara att använda sökfunktionen, och offset funktionen, dessa tillsammans söker efter den tidigare tänkte kolumnen och populerar sen sin formel efter denna.

Nästa problem är nu att kunna köra "Define Name" på kolumner, också det genom Find funktionen eftersom jag inte vill behöva ange en exakt range.

Får gnugga vidare lite på internet och se om jag lyckas komma på det.

Lyckades få till en lösning:

Första raden tar bort befintligt namn (eftersom kolumnen kan ha ändrats)
Övriga rader söker efter rätt kolumn, väljer hela kolumen och applicerar nytt namn för kolumnen.

Citat:

' Serverlist Short Hostname

ActiveWorkbook.Names("ServerlistShortHostname").De lete

Range("A1").Select
Cells.Find(What:="Short Hostname").Activate
ActiveCell.EntireColumn.Select
ActiveWorkbook.Names.Add Name:="ServerlistShortHostname", RefersTo:=Selection

MasterChief · 2015-09-22, 16:02

Du behöver ju uppenbarligen inte min hjälp längre.

Bra utveckling!

no aim · 2015-09-23, 21:47

Någon som vet om det är möjligt att importera excel tabell, med all dess formler och uträkingar till en Access databas?

martikainen · 2015-09-24, 07:30

Citat:

Ursprungligen postat av MasterChief

Du behöver ju uppenbarligen inte min hjälp längre.

Bra utveckling!

haha om man bara hittar ett bra sätt att börja på, i mitt fall att definera 1 cell som fast och utgå från denna med find+offset, då släpper ju det mesta

La till några moduler som sätter designen med olika färger och formateringar, så nu kan jag enkelt lägga till en ny "kolumn" (dvs en snutt kod) i dokumentet och den kommer automatiskt landa där jag vill, övriga kolumner ändras ingenting förutom i vilken faktiskt kolumn de landar i, och alla delar skiljs åt av färger.

Det jag skulle vilja göra är ju att använda grouping på något bra sätt så att jag kan få en översikt.

Just nu har jag tex dessa rubriker, som sedan har en del information under sig som man skulle vilja dölja

Virtual platforms
- CPU
- Minne
- Physical host
- VLANs
Backup
- Backupsystem
- Backupstorlek
Monitoring
- Status
- typ av övervakning

Det hade varit snyggt att kunna få groupings på dessa så att man får en snabb översikt, som visar att servern finns i Virtual Platforms, Backup, Övervakning osv. Och att man sedan kan expandera för att se den detaljerade informationen.

hahavaffan · 2015-11-13, 08:40

Jag har knåpat ihop ett gött Excel-ark som gör en grej som vårt kära affärssystem inte klarar av. Jag har tre blad, där två av dom hämtar data ur SQL-server till pivottabeller, och ett summerar och visar informationen som användaren vill ha. Ett av bladen har en formel som efter varje uppdatering av data från databaserna kör en beräkning som är lite för slö. Det tar ungefär 2 minuter på en quad core 3.4ghz i7 att beräkna, och jag tänker att ni kanske kan se hur jag kan förbättra den?

Här är formeln:

Kod:

=OMFEL(INDEX(Inventory!$C$4:$C$30000;PASSA(B4;Inventory!$D$4:$D$30000;0));"0")

Ni kan ignorera OMFEL, för den adderar ingen tid. Men det formeln gör är att med INDEX och PASSA matcha ett artikelnummer med ett artikelnummer i ett annat blad, och vid träff plocka data ur en cell i det andra bladet. Det är ungefär 25.000 rader i det ena och 15-20.000 i det andra, så det är ju en del rader att snurra igenom.
Min teori är att den för varje rad i första bladet snurrar igenom varje rad i det andra, vilket blir en jävla massa onödigt letande, eftersom att det hade räckt om snurran gick vidare till nästa artikelnummer efter första träffen. Förstår ni hur jag menar?

4623 · 2015-11-13, 08:51

Kan du inte använda VBA?

hahavaffan · 2015-11-13, 10:24

Citat:

Ursprungligen postat av 4623

Kan du inte använda VBA?

Jag har gjort det mesta i bladet med VBA, så jo. Men utveckla gärna hur du tänker

4623 · 2015-11-13, 10:28

Jag sysslar inte speciellt mycket med excel. Men detta kanske kan hjälpa dig en bit vägen?

https://msdn.microsoft.com/en-us/lib...(v=vs.90).aspx

Sen kan du kolla upp binary tree också.

MasterChief · 2015-11-13, 12:01

Citat:

Ursprungligen postat av hahavaffan

Jag har knåpat ihop ett gött Excel-ark som gör en grej som vårt kära affärssystem inte klarar av. Jag har tre blad, där två av dom hämtar data ur SQL-server till pivottabeller, och ett summerar och visar informationen som användaren vill ha. Ett av bladen har en formel som efter varje uppdatering av data från databaserna kör en beräkning som är lite för slö. Det tar ungefär 2 minuter på en quad core 3.4ghz i7 att beräkna, och jag tänker att ni kanske kan se hur jag kan förbättra den?

Här är formeln:

Kod:

=OMFEL(INDEX(Inventory!$C$4:$C$30000;PASSA(B4;Inventory!$D$4:$D$30000;0));"0")

Ni kan ignorera OMFEL, för den adderar ingen tid. Men det formeln gör är att med INDEX och PASSA matcha ett artikelnummer med ett artikelnummer i ett annat blad, och vid träff plocka data ur en cell i det andra bladet. Det är ungefär 25.000 rader i det ena och 15-20.000 i det andra, så det är ju en del rader att snurra igenom.
Min teori är att den för varje rad i första bladet snurrar igenom varje rad i det andra, vilket blir en jävla massa onödigt letande, eftersom att det hade räckt om snurran gick vidare till nästa artikelnummer efter första träffen. Förstår ni hur jag menar?

Jag kan inte riktigt ur din förklaring se behovet av att köra PASSA/INDEX istället för LETARAD? Det är ju en exakt matchning du vill ha så då brukar ju LETARAD funka bäst. Fördelen är ju att det är en funktion så jag tror den är snabbare än att köra först en PASSA och sedan en INDEX. Sen om man sätter "0" som typ i PASSA borde den väl gå vidare till nästa artikelnummer efter första träffen? Jag vet att LETARAD gör det i alla fall.

MasterChief · 2015-11-13, 12:16

Nä, jag har nog missuppfattat ditt exempel hahavaffan för när jag satte upp två listor med 25000 nummer i varje (på varsit blad) och bad Excel hitta aktuellt nummer i ena listan och matcha det med den andra listan och sedan hämta upp namnet bredvid det numret så gick det ju fort så in i helvete.

EDit: även när jag lät slumpgeneratorn ordna alla 50.000 nummer så tog det ju bara 2-3 sekunder för att köra en matchning.

Supermicke · 2015-11-17, 19:06

x f y
1| 2 5 1
2| 3 5 2
3| 5 6 3
4| 9 5 4
5| 3 3 6

hej har en liten fråga om utformningen av en lösning

exempelvis..
vill göra sumproduct(x1:x5;y1:y5) för värdet där f är säg 5.. antar att det finns någon fiffig lösning på det med någon sorts sats.. (for f1 till f5 do sumproduct if f=5 else "ta nästa") .. XD så tänker jag men kan inte formulera det

Pikhult · 2015-11-17, 21:28

Citat:

Ursprungligen postat av Supermicke

x f y
1| 2 5 1
2| 3 5 2
3| 5 6 3
4| 9 5 4
5| 3 3 6

hej har en liten fråga om utformningen av en lösning

exempelvis..
vill göra sumproduct(x1:x5;y1:y5) för värdet där f är säg 5.. antar att det finns någon fiffig lösning på det med någon sorts sats.. (for f1 till f5 do sumproduct if f=5 else "ta nästa") .. XD så tänker jag men kan inte formulera det

Nåt i stil med =sumproduct((x1:x5)*(f1:f5=5);(y1:y5)*(f1:f5=5)) borde väl lösa problemet.
Om det finns något tjusigare sätt att komma fram till samma sak låter jag dock vara osagt...

Supermicke · 2015-11-18, 21:12

funkade ju fan

jag är djupt imponerad!! älskar smarta lösningar i excel