Kolozzeum Forum - Sveriges största träningsforum
Gammal 2015-07-18, 22:21   #31
Jockefoten
Registered User
 
Jockefotens avatar
 
Reg.datum: Dec 2008
Inlägg: 442
Citat:
Ursprungligen postat av Jockefoten Visa inlägg
Hej forumet!

Jag har gjort en logik av två tabeller. A är tryck och B är temperatur. Jag vill i en annan cell ha en rullista där jag väljer ett tryck för att sedermera i cellen jämte ska temperaturen redovisas. Jag förstår logiken bakom det och har gjort ett schema som ser ut såhär:
I cell O31 har jag lagt in detta:
=IF(N31=A1;B1;IF(N31=A2;B2))
Problemet är att jag har typ 200 celler med data som ska matas in. Finns det en bättre formel om man vill behålla allt i en cell?
Jag löste det (undvik hur pass sorgligt det är att jag sitter och knapprar Excel en lördagsnatt).

Man använder "sumif".

Så enligt min data ovan så blir det:
=SUMIF(A1:A100;N31;B1:B100)
Jockefoten är inte uppkopplad   Svara med citat
Gammal 2015-09-16, 17:36   #32
martikainen
Lever på gamla meriter
 
martikainens avatar
 
Reg.datum: May 2008
Ort: Örebro
Inlägg: 3 152
Jag skulle behöva lite stöttning i VBScript, jag har ett ganska stort excelprogram som fungerar som en slags informationsdatabas.

Jag har ett 20 tal flikar med information som hämtas från olika källor, och på en sida har jag byggt en sammanställning.

Den bygger på att man skriver in ett namn i kolumn A, som sedan slås upp mot alla andra flikar för att hämta diverse information,

I dagsläget har jag gjort så att rad 3 på sammanställningsfliken är dedikerad för den formeln som skall hämta data, denna kommer sedan autofillas nedåt beroende på hur många rader det finns i kolumn A.
Det jag skulle vilja göra är att skriva ut koden via VBScript istället (för att få bort koden från själva dokumentet, blir mer lätthanterat då.


Så här ser mina kod ut i dagsläget

' This part count rows in column A then uses autofill to populate formulas on Row 3 to columns C-DE

Dim LR As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.CutCopyMode = False
Range("C3G3").AutoFill Destination:=Range("C3G" & LR), Type:=xlFillDefault


Jag skulle vilja använda något likt detta


lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C3").Formula = "=$L$1/$L$2"
Range("C3").AutoFill Destination:=Range("C3:C" & lastRow)


Men så fort jag anger en avancerad formel, tex en =IF, då får jag felmeddelandet "Run-Time error'1004': Application-defined or object-defined error"


Formeln jag försökte med var till och med förenklad så att den ej räknade rader i kolumn A

Range("D4").Formula = "=IF(AA3="";"";AA3)"
Range("D420").FillDown

Ni får ursäkta smilegubbarna, det ska självklart vara " : D " där det är en smilegubbe.
__________________
Pensionerad
martikainen är inte uppkopplad   Svara med citat
Gammal 2015-09-16, 18:51   #33
MasterChief
Världens Största Kyk®
 
MasterChiefs avatar
 
Reg.datum: Jan 2004
Inlägg: 5 068
Nu var det ett tag sedan jag fulkodade i VBA men ska det verkligen vara semikolon i IF-formeln i VBA? Tror det måste vara komma-tecken?
MasterChief är inte uppkopplad   Svara med citat
Gammal 2015-09-16, 19:06   #34
Jockefoten
Registered User
 
Jockefotens avatar
 
Reg.datum: Dec 2008
Inlägg: 442
Jag är supern00b på VBA men fungerar vanlig IF i VBA?
Är det inte ett IF-Then-Else man måste skriva?

Typ:
Kod:
If condition_1 Then
   result_1

ElseIf condition_2 Then
  result_2
  
...
            
ElseIf condition_n Then
   result_n

Else
   result_else

End If
Jockefoten är inte uppkopplad   Svara med citat
Gammal 2015-09-16, 19:12   #35
MasterChief
Världens Största Kyk®
 
MasterChiefs avatar
 
Reg.datum: Jan 2004
Inlägg: 5 068
Citat:
Ursprungligen postat av Jockefoten Visa inlägg
Jag är supern00b på VBA men fungerar vanlig IF i VBA?
Är det inte ett IF-Then-Else man måste skriva?

Typ:
Kod:
If condition_1 Then
   result_1

ElseIf condition_2 Then
  result_2
  
...
            
ElseIf condition_n Then
   result_n

Else
   result_else

End If
Han försöker få skriptet att skriva in en formel i en cell i Bladet och det går såklart. Men VBA har en annan syntax än funktionshanteringen i Bladen och semikolon funkar inte vad jag minns.

Här fanns lite om detta:

http://www.ozgrid.com/forum/showthread.php?t=145204
MasterChief är inte uppkopplad   Svara med citat
Gammal 2015-09-17, 07:39   #36
martikainen
Lever på gamla meriter
 
martikainens avatar
 
Reg.datum: May 2008
Ort: Örebro
Inlägg: 3 152
Citat:
Ursprungligen postat av MasterChief Visa inlägg
Nu var det ett tag sedan jag fulkodade i VBA men ska det verkligen vara semikolon i IF-formeln i VBA? Tror det måste vara komma-tecken?
Citat:
Ursprungligen postat av MasterChief Visa inlägg
Han försöker få skriptet att skriva in en formel i en cell i Bladet och det går såklart. Men VBA har en annan syntax än funktionshanteringen i Bladen och semikolon funkar inte vad jag minns.

Här fanns lite om detta:

http://www.ozgrid.com/forum/showthread.php?t=145204
Jag tänkte inte alls på att en formel kanske inte fungerar likadant i VBA som det gör i excel, ska testa skriva om det och se om det fungerar
__________________
Pensionerad
martikainen är inte uppkopplad   Svara med citat
Gammal 2015-09-17, 09:47   #37
MasterChief
Världens Största Kyk®
 
MasterChiefs avatar
 
Reg.datum: Jan 2004
Inlägg: 5 068
Citat:
Ursprungligen postat av martikainen Visa inlägg
Jag tänkte inte alls på att en formel kanske inte fungerar likadant i VBA som det gör i excel, ska testa skriva om det och se om det fungerar
Om du testar att generera kod med hjälp av inspelningsfunktionen ("spela in makro") och skriver en IF-formel i en cell så kan du sedan se att koden blev:

ActiveCell.FormulaR1C1 = "=+IF(RC[-2]=2,1,4)"

Det intressanta är ju att den gjorde om "semikolon" (som jag använde när jag skrev in formeln i bladet) till just "komma" (sedan gör den ju även om cellreferensen men det är ju för att makroinspelningen använder sig av FormulaR1C1 propertyn och det går ju att ändra exempelvis till absoluta referenser).

När jag sedan kör detta lilla makro så generar den en fungerade IF-formel i cellen (med semikolon).

Senast redigerad av MasterChief den 2015-09-17 klockan 09:51.
MasterChief är inte uppkopplad   Svara med citat
Gammal 2015-09-17, 11:41   #38
martikainen
Lever på gamla meriter
 
martikainens avatar
 
Reg.datum: May 2008
Ort: Örebro
Inlägg: 3 152
Citat:
Ursprungligen postat av MasterChief Visa inlägg
Om du testar att generera kod med hjälp av inspelningsfunktionen ("spela in makro") och skriver en IF-formel i en cell så kan du sedan se att koden blev:

ActiveCell.FormulaR1C1 = "=+IF(RC[-2]=2,1,4)"

Det intressanta är ju att den gjorde om "semikolon" (som jag använde när jag skrev in formeln i bladet) till just "komma" (sedan gör den ju även om cellreferensen men det är ju för att makroinspelningen använder sig av FormulaR1C1 propertyn och det går ju att ändra exempelvis till absoluta referenser).

När jag sedan kör detta lilla makro så generar den en fungerade IF-formel i cellen (med semikolon).
Perfekt lösning Chefen! Tusen tack

Får sitta ikväll och bygga om mitt program och se så den slår korrekt på alla formler.

Det som blir lite jobbigare är att sätta sig in i hur den nya typen av formel fungerar och hur kan jag editera den enkelt.

=IF(AA3="";"";AA3)
Blev
Range("D4").Formula = "=IF(RC[23]="""","""",RC[23])"

Medans

=IFNA(VLOOKUP(AO5;VirtualPlatforms!A:B;2;FALSE);"" )
Blev
"=IFNA(VLOOKUP(RC[36],VirtualPlatforms!C[-4]:C[-3],2,FALSE),"""")"

Inte helt med på vad de olika [] hänvisar till, får läsa på lite där. Det händer rätt ofta att jag lägger med en kolumn någonstans i sammanställning och måste därmed kunna byta i vilken kolumn de olika formlerna populeras i.
__________________
Pensionerad

Senast redigerad av martikainen den 2015-09-17 klockan 11:42.
martikainen är inte uppkopplad   Svara med citat
Gammal 2015-09-17, 12:17   #39
MasterChief
Världens Största Kyk®
 
MasterChiefs avatar
 
Reg.datum: Jan 2004
Inlägg: 5 068
Citat:
Ursprungligen postat av martikainen Visa inlägg
Perfekt lösning Chefen! Tusen tack

Får sitta ikväll och bygga om mitt program och se så den slår korrekt på alla formler.

Det som blir lite jobbigare är att sätta sig in i hur den nya typen av formel fungerar och hur kan jag editera den enkelt.

=IF(AA3="";"";AA3)
Blev
Range("D4").Formula = "=IF(RC[23]="""","""",RC[23])"

Medans

=IFNA(VLOOKUP(AO5;VirtualPlatforms!A:B;2;FALSE);"" )
Blev
"=IFNA(VLOOKUP(RC[36],VirtualPlatforms!C[-4]:C[-3],2,FALSE),"""")"

Inte helt med på vad de olika [] hänvisar till, får läsa på lite där. Det händer rätt ofta att jag lägger med en kolumn någonstans i sammanställning och måste därmed kunna byta i vilken kolumn de olika formlerna populeras i.
RC-propertyn kan vara ett elände och därför är det alltid lättare att lära sig skriva koden själv när det gäller just funktioner. Makroinspelningen skapar även otroligt mycket onödig kod som inte behövs. Just RC[23] är bara en relation till vilken cell som är markerad när du spelar in makrot. Alltså inte helt dugligt i alla lägen. Men inspelningsfunktionen tjänar ändå ett syfte om man snabbt vill ha koden för till exempel formatering av celler. Det är en plåga att skriva själv och man får mycket av grundstrukturen gratis. När jag kodade så använde jag just inspelningen som komplement vilket ju är enkelt då den skapar en egen modul med allt väl avgränsat. Sedan är det bara att plocka ut godbitarna.

Att lägga till rader och kolumner och sedan ha VBA-kod är inte en god idé om man använder sig av specifika cellreferenser. Det finns en anledning att många xlsm-filer är så låsta. Räcker med att man infogar en liten cell så är koden förbrukad. Men det går ju att komma runt genom att inte använda sig av referenser till specifika celler utan man låter skriptet generera detta själv genom att räkna ut vilken cell som är aktuell och sedan använder denna i formeln.
MasterChief är inte uppkopplad   Svara med citat
Gammal 2015-09-17, 15:32   #40
martikainen
Lever på gamla meriter
 
martikainens avatar
 
Reg.datum: May 2008
Ort: Örebro
Inlägg: 3 152
Citat:
Ursprungligen postat av MasterChief Visa inlägg
RC-propertyn kan vara ett elände och därför är det alltid lättare att lära sig skriva koden själv när det gäller just funktioner. Makroinspelningen skapar även otroligt mycket onödig kod som inte behövs. Just RC[23] är bara en relation till vilken cell som är markerad när du spelar in makrot. Alltså inte helt dugligt i alla lägen. Men inspelningsfunktionen tjänar ändå ett syfte om man snabbt vill ha koden för till exempel formatering av celler. Det är en plåga att skriva själv och man får mycket av grundstrukturen gratis. När jag kodade så använde jag just inspelningen som komplement vilket ju är enkelt då den skapar en egen modul med allt väl avgränsat. Sedan är det bara att plocka ut godbitarna.

Att lägga till rader och kolumner och sedan ha VBA-kod är inte en god idé om man använder sig av specifika cellreferenser. Det finns en anledning att många xlsm-filer är så låsta. Räcker med att man infogar en liten cell så är koden förbrukad. Men det går ju att komma runt genom att inte använda sig av referenser till specifika celler utan man låter skriptet generera detta själv genom att räkna ut vilken cell som är aktuell och sedan använder denna i formeln.
haha ja, om man inte är ensam att jobba i ett dokument så är det ju ungefär 100% sannolikhet att dokumentet är förstört efter 4h

Nu är detta dokument enbart till för mig själv och för att skapa rapporter åt andra, men bara jag som arbetar i det.

Hur menar du med att låta scriptet generera det själv? Jag kanske borde skicka med en kopia på mitt dokument så att du förstår hur det funkar

Men egentligen

Kolumn A & B är referensvärdet, dvs det alla formler börjar med att söka ifrån (IF sats som tar första värdet bara, saknas A, tas B)
I mitt fall ett servernamn, värde A är oftast FQDN, medans värde B är hostnamn.

Kolumn C-M information från system X
Kolumna N-W information från System Y

Så länge jag ej flyttar på kolumn A eller B så kommer med andra ord mina formler alltid att ha rätt fält att söka i, och populerar jag koden via VBScript så hamnar koden alltid i rätt kolumn.

Formlerna söker oftast flera gånger mot dessa kolumner, tex denna kod

Kolumn H (första sökningen)
=IFERROR(IFNA(VLOOKUP(A4;CMDB!$A$2:$A$8000;1;FALSE );(VLOOKUP(B4&"*";CMDB!$A$2:$A$8000;1;FALSE)));"")

Kolumn I (Andra sökningen)
=IFERROR(IFNA(VLOOKUP(A4;CMDB!$A$2:$A$8000;1;FALSE );(VLOOKUP("*"&B4&"*";CMDB!$A$2:$A$8000;1;FALSE))) ;"")

Just av den anledningen att alla våra system använder ej FQDN, ibland har det smygit sig in någon prefix, eller något annat efter hostnamn än vad som borde vara där.


Överlag tror jag att jag borde bygga mina sökningar mot INDEX funktionen istället, men jag har inte riktigt greppat den, har använt den på IP-adresser där man oftast har fler än 1 med samma FQDN som referensvärde.
Servernamn är dock alltid unika.

Med det sagt så har jag i alla fall fått till formelpopuleringen via VB nu, alla formler populeras i första raden, och sedan körs en count på antal rader i kolumn A, och efter det kör den AutoFill på alla kolumner, motsvarande antal rader. Så funktionen är uppnådd Nu ska jag nog bara fortsätta optimera koden.

Samt försöka ett bättre sätt att lagra datat, just nu ligger det i enskilda flikar i dokumentet, bättre vore att datat hämtades från .CSV filer (vilket är orginaldatat) från en folder på min PC tex, så behöver jag inte ens uppdatera det, bara byta ut filerna.

Eller finns det bättre lösningar, exceldatabas körde jag förut men gick ifrån det av någon anledning.
__________________
Pensionerad
martikainen är inte uppkopplad   Svara med citat
Gammal 2015-09-17, 18:42   #41
MasterChief
Världens Största Kyk®
 
MasterChiefs avatar
 
Reg.datum: Jan 2004
Inlägg: 5 068
Citat:
Ursprungligen postat av martikainen Visa inlägg

Hur menar du med att låta scriptet generera det själv? Jag kanske borde skicka med en kopia på mitt dokument så att du förstår hur det funkar

Eller finns det bättre lösningar, exceldatabas körde jag förut men gick ifrån det av någon anledning.
Enkelt uttryckt:

Om du har formeln IF(C5=1;1;0) så har du ett problem om du "ändrar positionen" på A5 genom att du till exempel lägger in en kolumn. Bladet ändrar sig men inte koden.

Men istället för att skriva C5 så kan du ersätta den med:

Dim Startcell as String

'Leta efter första tomma cellen i raden
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select

Startcell = ActiveCell.Select

'Sedan är de bara att anropa variabeln i formeln och det är typ såhär

IF(" & Startcell & "=1,1,0)


Brasklapp för att fulkoden inte är helt korrekt, men du förstår nog vad jag menar. Leta upp rätt cell, sätt den som variabel och anropa den i formeln. Googla om variabler och formler i VBA Excel så hittar du en hel del hur du skapar formeln på ett korrekt sätt.

För övrigt är jag inte så mycket för eleganta lösningar och kör ofta rakt på. Men det har alltid räckt för mina behov. Excel erbjuder otroligt mycket funktionalitet som man aldrig använder och då använder jag ändå massor av saker som gemene excelanvändare inte ens vet att det finns.

Senast redigerad av MasterChief den 2015-09-17 klockan 18:44.
MasterChief är inte uppkopplad   Svara med citat
Gammal 2015-09-18, 16:13   #42
martikainen
Lever på gamla meriter
 
martikainens avatar
 
Reg.datum: May 2008
Ort: Örebro
Inlägg: 3 152
Citat:
Ursprungligen postat av MasterChief Visa inlägg
Enkelt uttryckt:

Om du har formeln IF(C5=1;1;0) så har du ett problem om du "ändrar positionen" på A5 genom att du till exempel lägger in en kolumn. Bladet ändrar sig men inte koden.

Men istället för att skriva C5 så kan du ersätta den med:

Dim Startcell as String

'Leta efter första tomma cellen i raden
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select

Startcell = ActiveCell.Select

'Sedan är de bara att anropa variabeln i formeln och det är typ såhär

IF(" & Startcell & "=1,1,0)


Brasklapp för att fulkoden inte är helt korrekt, men du förstår nog vad jag menar. Leta upp rätt cell, sätt den som variabel och anropa den i formeln. Googla om variabler och formler i VBA Excel så hittar du en hel del hur du skapar formeln på ett korrekt sätt.

För övrigt är jag inte så mycket för eleganta lösningar och kör ofta rakt på. Men det har alltid räckt för mina behov. Excel erbjuder otroligt mycket funktionalitet som man aldrig använder och då använder jag ändå massor av saker som gemene excelanvändare inte ens vet att det finns.
jag är med på vad du menar, jag tror dock inte att jag förstår hur det skulle lösa mina problem helt och hållet, problemet är att det inte bara är C5 (tex) som jag använder som VLOOKUP värde.

Jag har suttit och lekt lite med att inte definera vart jag populerar mina formler (dvs i vilken kolumn) utan att istället använda mig av

Range("A3:M3").Find("").Select

Selection.Value = "formel"


Detta skulle innebära att varje formel placerades ut automatiskt i nästa lediga kolumn, om jag dock lägger till en formel mitt i så kommer ju en formel som tidigare pekade på att söka i C5 att fallera om den flyttat till C5.

Det jag skulle behöva är ju ett sätt att definera den kolumnen som huserar de olika LOOKUP värdena.

I mitt fall så har jag 12 olika kolumner som de andra kolumnerna söker mot.

jag skickar ett PM med dokumentet så förstår du strukturen kanske
__________________
Pensionerad
martikainen är inte uppkopplad   Svara med citat
Gammal 2015-09-18, 17:12   #43
MasterChief
Världens Största Kyk®
 
MasterChiefs avatar
 
Reg.datum: Jan 2004
Inlägg: 5 068
Kan kolla på den på måndag då jag har tillgång till Excel.
MasterChief är inte uppkopplad   Svara med citat
Gammal 2015-09-18, 17:51   #44
martikainen
Lever på gamla meriter
 
martikainens avatar
 
Reg.datum: May 2008
Ort: Örebro
Inlägg: 3 152
Tror jag hittade en lösning på hur jag kan göra det.

Om jag tar "Define Name" på kolumn A och sätter den till ServerFQDN
Och sen gör jag samma sak på den andra fliken där den ska söka och definerar kolumn A (där motsvarande servernamn finns) till CMDBFQDN.

Körde sedan formeln likt detta =IFNA(VLOOKUP(ServerFQDN;CMDBFQDN;1;FALSE);"")

Och det verkar ha fungerat! Ska testa om jag kan definera namn via VBscript också så att jag kan populera formlerna, definera namnet och få det att fungera.
__________________
Pensionerad
martikainen är inte uppkopplad   Svara med citat
Gammal 2015-09-19, 10:02   #45
martikainen
Lever på gamla meriter
 
martikainens avatar
 
Reg.datum: May 2008
Ort: Örebro
Inlägg: 3 152
Lösningen ovan fungerade väldigt bra och mina formler söker nu på Namn, istället för faktiskta kolumner.

Om jag skulle ta det ett steg längre så skulle jag nu populera mitt dokument utan att ange någon range på vart det ska hamna.

I dagsläget ser koden ut såhär

Range("A1") = "FQDN"
Range("A:A").Select
ActiveWorkbook.Names.Add Name:="serverlistFQDN", RefersToR1C1:= _
"=Serverlist!C1"


Den skriver alltså text i A1, och definerar sedan kolumn A:A med namnet "ServerlistFQDN" så att mina formler kan söka mot detta namn.

Istället skulle jag vilja göra en sökfunktion som

1. Söker efter första tomma cellen i rangen A1:M1 tex.
2. Skriver ut texten "FQDN" i den tomma cellen
3. Definerar namnet på hela kolumnen som den valt.

Googlar för fullt på hur man använder find, och kan definera resultatet som en variabel och sedan anropa detta när den skall trycka ut uppgift 2 & 3 tex.

Skulle jag få till det så kan jag enkelt lägga till nya kolumner vart som helst i dokumentet och jag behöver då inte ändra någon annan formel eftersom den varje gång kommer leta efter den första tomma cellen i A1


Del 1 lyckades jag lösa, kanske inte den snyggaste koden men

Dim rngLast As Range

With ThisWorkbook.Worksheets("Serverlist")
If .Range("A1").Value = "" Then
Set rngLast = .Range("A1")
Else
Set rngLast = .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1)
End If
rngLast.Value = "FQDN"

End With


Men sen kommer del 2 att söka efter "FQDN" första raden och sedan tex populera en formel, samt definera hela den kolumnen med ett namn

Koden nedan för att definera namn på kolumn
ActiveWorkbook.Names.Add Name:="serverlistFQDN", RefersToR1C1:= _
"=Serverlist!C1"
__________________
Pensionerad

Senast redigerad av martikainen den 2015-09-19 klockan 10:07.
martikainen är inte uppkopplad   Svara med citat


Svara

Ämnesverktyg
Visningsalternativ

Regler för att posta
Du får inte posta nya ämnen
Du får inte posta svar
Du får inte posta bifogade filer
Du får inte redigera dina inlägg

BB-kod är
Smilies är
[IMG]-kod är
HTML-kod är av

Forumhopp


Alla tider är GMT +1. Klockan är nu 16:00.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Svensk översättning av: Anders Pettersson
Copyright 2015, Kolozzeum (TM)