Macro to find, replace text and calculate VAT in a cell

**elgato74** · 10-25-2014, 07:08 AM

Dear All,

please have a look at the attached workbook. Each sheet contains an Invoice. Each invoice has a cell containing "Totale ordine: EUR xx,xx".

A macro should add the following to this cell content: after the amount, it should say "inc. EUR x,xx VAT 22%"

I assume the first step is to find and replace the "Totale ordine: EUR" to have just a number in that cell so it can be calculated. This number should then be devided by 1,22 to get the net sum. The diffence between the total and the net amount is then the VAT amount. This amount should be inserted into the cell together with the above text.

I hope this can be done with just one macro.

Thanks muchly !!!

Felix

**HaHoBe** · 10-25-2014, 08:51 AM

Hi, Felix,

maybe try:

Please Login or Register  to view this content.

Please mind that this macro will only run on the first try and not adapt values if they are changed as I didn´t see that as a requirement here.

Ciao,
Hiolger

**elgato74** · 10-25-2014, 09:05 AM

Hi Holger

thank you ! so far so good, but a little mistake : the amount is only shown as the net amount ( netto betrag ) however, i need the tax amount, the difference between the brutto and net amount, e.g. on the first sheet: 59,93 Euro - 49,12 Euro = 10,81 Euro. So the cell should read: "Totale ordine: EUR 59,93 inc. EUR 10,81 VAT 22%"

**elgato74** · 10-25-2014, 09:18 AM

so the whole cell should read:

Totale ordine: EUR 59,93 ( net. EUR 49,12 zzgl. EUR 10,81 VAT 22%)

**HaHoBe** · 10-25-2014, 09:19 AM

Hi, Felix,

just replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Ciao,
Holger

**elgato74** · 10-25-2014, 09:24 AM

oops, did you see my last post:

the cell should read: "Totale ordine: EUR 59,93 (net. EUR 49,12 zzgl. EUR 10,81 VAT 22%)"

blöde deutsche Steuergesetze

der Nettobetrag muss auch ausgewiesen sein

**HaHoBe** · 10-25-2014, 09:48 AM

Hi, Felix,

instead of

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I wonder if Italians will know the meaning of zzgl.

Ciao,
Holger

**elgato74** · 10-25-2014, 09:53 AM

Hi Holger, great ! Works well except for a Runtime Error 91 - refering to the dblBase line ...

**HaHoBe** · 10-25-2014, 09:58 AM

Hi, Felix,

instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Ciao,
Holger

**elgato74** · 10-25-2014, 10:00 AM

perfect !!! One last little thing before I can wish you a happy weekend. How can I align this specific cell to the right ? Because it gets sooo long and it exceeds the print area ?

**HaHoBe** · 10-25-2014, 10:22 AM

Hi, Felix,

you could use the macrp-recorder to get the basic code for this action.

Add the code-line in blue:

Please Login or Register  to view this content.

Ciao,
Holger

**elgato74** · 10-25-2014, 10:27 AM

Super ! Thank you so much Holger ! Bon weekend !

**elgato74** · 10-25-2014, 03:37 PM

sorry for having to reopen this. is there a way to automatically create a new sheet or workbook with all the net amounts added up ? E.g. new sheet A1:49,12 B1:49,12 / A2 49,12 B2: 98,24 and so on. So in Column B always the accumulated amount from each net amount in each invoice. I need this to check when I reach a certain amount.

Edit:

Probably best with a second macro

**HaHoBe** · 10-26-2014, 04:23 AM

Hi, Felix,

you should take some time considering what you want prior to starting a thread and amending requests when the before last result is available - I loose my interest in this way of "Flickschusterei".

Probably best with a second macro

If a second macro it would be a new thread, and you would have to loop through the sheets again and do the same calculations but only in a longer string while in the original macro the data is available as dblBase.

This macro will cut any available sting to the original length and start on it again., If you feel you want to keep the summaray sheet just alter the code to check the availability of the summary sheet and then decide whether to add a new one or delete the entries from A2 to the "C" & Rows.Count:

Please Login or Register  to view this content.

Ciao,
Holger

**elgato74** · 10-26-2014, 07:24 AM

Hello Holger, yes you are certainly right. I apologize. That requirement came up after studying German tax laws

However, the macro only adds up to sheet 35 then stops. Probably because sheet 36 does not have the "Totale ordine" text, as it is on the next sheet 37. Can this be fixed ?

Felix

**HaHoBe** · 10-26-2014, 07:27 AM

Hi, Felix,

Please Login or Register  to view this content.

Ciao,
Holger

**elgato74** · 10-26-2014, 07:33 AM

Truly awesome Holger !

Can you point out to me the code that only does the summary, should i decide that i want this to be a second macro to run after the first one.

**HaHoBe** · 10-26-2014, 11:05 AM

Hi, Felix,

the last If-Statement posted by me, only that you would have to make sure to know how the contents of the cell looks like and maybe use Mid and the start as the end of the first search string and the end to be two characters before the opening bracket.

Ciao,
Holger

Macro to find, replace text and calculate VAT in a cell

LinkBack

Thread Tools

Rate This Thread

Display

Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Re: Macro to find, replace text and calculate VAT in a cell

Thread Information

Users Browsing this Thread

Similar Threads

Creating a macro to find and replace text

Macro required to find text in a specified cell and replace it with text iin another cell

[SOLVED] Macro to copy cell contents then used Find & Replace to put text elswhere.

find in excel replace in word: find/replace text in text boxes and headers

Creating a macro to find and replace text

Bookmarks

Bookmarks

Posting Permissions