Redigera formel med VBA

[cowi]

Hej

 

Jag har ett arbetsark med ganska många länkar till andra filer.

Om en cell som jag länkat till är tom får länken en nolla som resultat, detta vill jag undvika genom att redigera in en Omsats i formlerna för en del kolumner.

 

Jag hade tänkt att ett makro som stegar igenom kolumnen och kompletterar formlerna individuellt för varje länk.

 

Exempel en länk ser ut så här =[Test.xls]BAS!$N$9 efter redigering vill jag att formeln skall se ut så här

=Om([Test.xls]BAS!$N$9="";"";[Test.xls]BAS!$N$9)

 

Jag får inte till det i VBA

 

Tacksam för hjälp

cowi

[Monshi]

Gör en sök&ersätt manuellt istället. Det är enklare.

 

Varför vill du göra det via VBA?

[Mr Andersson]

Hej

 

Jag har ett arbetsark med ganska många länkar till andra filer.

Om en cell som jag länkat till är tom får länken en nolla som resultat, detta vill jag undvika genom att redigera in en Omsats i formlerna för en del kolumner.

 

Jag hade tänkt att ett makro som stegar igenom kolumnen och kompletterar formlerna individuellt för varje länk.

 

Exempel en länk ser ut så här =[Test.xls]BAS!$N$9 efter redigering vill jag att formeln skall se ut så här

=Om([Test.xls]BAS!$N$9="";"";[Test.xls]BAS!$N$9)

 

Jag får inte till det i VBA

 

Tacksam för hjälp

cowi

 

 

Om du vill skriva formler i VBA, så är det Formula (eller FormulaLocal)-egenskapen du ska använda:

 

range("a1:a10").Formula = "=sum(b2:b3)"

 

Om du vill använda fnuttar (") i en formel, så skrivs dessa som dubbelfnuttar eftersom en enkelfnutt kommer avsluta strängen. Dubbelfnuttarna i ditt exempel blir """".

 

Skillnaden mellan Formula och FormulaLocal, är att med Formula skriver du engelsk syntax, med FormulaLocal skriver du formler på det språk som ditt Excel har. Internt i Excel kommer de att sparas som engelska i vilket fall som, men ibland är det enklare att skriva formler på sitt eget språk.

[cowi]

Tack för svaren.

 

Jag vill göra det här med ett makro för att det är länkar från ca 10 filer och tillsammans 2000 rader.

 

Det jag inte kan är hur jag skall få formeln i cellen som en text till en variabel i VBA så att den går att komplettera och sedan lägga tillbaka.

 

cowi

[Mr Andersson]

Tack för svaren.

 

Jag vill göra det här med ett makro för att det är länkar från ca 10 filer och tillsammans 2000 rader.

 

Det jag inte kan är hur jag skall få formeln i cellen som en text till en variabel i VBA så att den går att komplettera och sedan lägga tillbaka.

 

cowi

 

Hämta formel från cell:

 

MinFormel = range("A1").formula

 

Gör vad du önskar med formlen. För att sätta tillbaka den ändrade formlen till cellen:

 

range("A1").formula = MinFormel

 

 

Tänk på att såna här saker alltid är vanskliga, eftersom du, teoretiskt sett, inte vet om det är en giltig formel eller ej som du försöker tilldela en cell.

[cowi]

Tack så mycket.

Jag har kommit en bit på väg trodde jag men det blir Program eller objektfel när jag skall lägga tillbaka den redigerade formeln.

 

Jag skickar med min testfil.

 

Hoppas på mer hjälp.

 

cowi

Göra om en formel med VBA.xls

[Monshi]

Min fråga är än: Varför använder du inte Excels egna sök&ersätt? varför ta extra steget över till VBA? Det är ju exakt det du försöker göra i koden!

 

Men men - en trestegs stege för att uppdatera en formel:

1: Spela in ett makro där du skriver in formeln du vill ha i en cell.

2: Skapa VBA-kod som söker igenom, och hittar, alla instanser du vill ändra. Kontrollera att den verkligen fungerar.

3: Ta koden från steg 1, sammanför den med koden i steg 2.

 

Men som sagt, varför inte använda Excel sök&ersätt?

Bara att

1: Markera område du vill söka inom.

2: Ta upp sök&ersätt dialogen

3: Skriv in söktermen (din formel du vill ändra, eller del av formeln som ska ändras)

4: Skriv in det nya som ska skrivas istället för det i punkt 3

5: Testa först genom att stega genom några träffar, tryck sedan Sök all.

 

En fördel med sök&ersätt är att det går att ångra om det blir fel. Om VBA-koden gör fel går det inte att ångra.

[cowi]

Den här filen med länkarna använder jag för att göra statistik, i några kolumner vill jag inte att länkens resultat skall vara noll om källcellen är tom därför har jag komletterat formlerna i dessa kolumner med en omsats manuellt efter att jag kopierat i länkarna.

Annars måste jag skriva in den rätta formeln innan jag kopierar ned för varje ny fil jag länkar.

 

Jag tänkte att ett makro snabbt skulle kunna lösa det momentet.

 

Min tanke var att ta texten i länken och komlettera med en omsats och sedan lägga tillbaka den som en formel i samma cell.

 

Sök och ersätt ser jag inte hur jag skulle använda i det här fallet eftersom det inte är något som skall ersättas.

 

Alla länkarna refererar till olika både celler och filer.

 

Kolla på koden i exemplet (modul 1) på föregående inlägg, där kan man se hur jag tänkt men jag har gjort något fel för Excel vill inte ta emot den redigerade formeln.

 

Har jag tänkt helt fel eller ?

 

cowi

[cowi]

Hittade felet glömde parenteser runt omsatsen och att använda FormulaLocal.

Den här koden funkade

 

NyFormel = "=Om(" & Länk & "="""";"""";" & Länk & ")"

 

ActiveCell.FormulaLocal = NyFormel

 

cowi