how to integrate a formula in my vba code

[hellownero]

[Bild: File-Copy-icon.png]

Code:

Sub filteradvan()
  Sheets("Hirata Bestellformular").Range("Tabelle3[[#Headers],[#Data]]"). _
      AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
      "Tabelle1!Criteria"), CopyToRange:=Range("A7:A8"), Unique:=False
End Sub

__________________________________________________________________

="WENNFEHLER(LINKS(A10;FINDEN(ZEICHEN(10);A10)-1);A10)"
English: =IFERROR(LEFT(A10;FIND(CHAR(10);A10)-1);A10)

_________________________________________________________________-

Hello guys[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
, Arial, Tahoma, Calibri, Geneva, sans-serif] I want to implement this formula in my vba code above....[/font][/size]
The code is using the advanced filter to create a table on an extra worksheet from a list on an other worksheet. It only displays the data which is fitting my criteria. It creates this: (1st picture)

imgur.com/a/gz6WZ (Das ist der Link zu den Bildern)

I can use the formula I displayed in german and english to transform my cells from picture 1 to the table in picture 2. What it does: The formula is just creating a cell with the first line of the linked cell.
I want to activate both things at the same time with a macro giving me just picture 2 as an output.
Can anyone help me?
Kind regards
hellownero

[schauan]

Hello,

cordial welcome in our forum. Please, upload your Pictures, tables or files directly here.

For Pictures you need one step more. At first you can search ("Durchsuchen" - I saw it but now, Button is only in german ) and upload ("Attachment hinzufügen" or "Add Attach"). As second step for a Picture you have to insert it into the post. Look at the Cursor Position, perhaps you can change it before you insert the picture.

First help for coding a formula is the macrorecorder. Insert the formula in a cell while Recorder is running, stop recording and look at the code. Don't Forget the "$" if you want absolutely cell adresses.

[hellownero]

Hello,

cordial welcome in our forum. Please, upload your Pictures, tables or files directly here.

For Pictures you need one step more. At first you can search ("Durchsuchen" - I saw it but now, Button is only in german ) and upload ("Attachment hinzufügen" or "Add Attach"). As second step for a Picture you have to insert it into the post. Look at the Cursor Position, perhaps you can change it before you insert the picture.

First help for coding a formula is the macrorecorder. Insert the formula in a cell while Recorder is running, stop recording and look at the code. Don't Forget the "$" if you want absolutely cell adresses.

Hello schauan,

thanks for your reply!!

how can you edit your post? I literally looked 5 mins for the edit button O_o.

yeah thats an good idea with the macro recorder and shouldnt be a problem but how do I integrate the recorded macro in my other macro in oder to skip the table created with advanced filter? So the result is just the table with the single lined cells?

Kind regards

hellownero

[schauan]

Hello,

you can edit your post during a period of 60 minutes but you have to finish your entrys and send the reply in this time.

ActiveCell.FormulaR1C1 = "=IFERROR(LEFT(R[9]C,FIND(CHAR(10),R[9]C)-1),R[9]C)"

The code insert the formula in local language p.e. in active cell

I don't see your macro and so I cant say anything about inserting in your Code, I don't see relationships between the formula and the advanced filter and I don't see your named range "Criteria" ... .

[snb]

Crossposted:

https://www.reddit.com/r/excel/comments/...orksheets/