Posts by danieltakeshi • 3,960 points
212 posts
-
0
votes3
answers11310
viewsA: Extract text with VBA
A Regular Expression can be used. Code Dim texto As String Dim objCorresp As Object, objExpReg As Object Set objExpReg = CreateObject("VBScript.RegExp") 'Expressão Regular With objExpReg .Pattern =…
-
0
votes1
answer2238
viewsA: VBA - Delete only last row of table containing text
Like the Listobject is being used, follow the code to delete the last line. Last row of table with value This is a code that creates an array of table values and performs a reverse loop (from the…
-
1
votes2
answers2117
viewsA: How do I change Image via VBA
Change Image is not exposed in object model, then a User Defined Function (UDF) can be created to solve this problem with a custom function. Function Function pseudo_alterar_imagem(caminho As…
-
1
votes2
answers114
viewsA: Replace numbers that are within a word
You can use this Regular Expression: \d+(?=[a-zA-Z]+)|(?<=[a-zA-Z])\d+ In which the demo of Regex101 can be seen. Code import re testes = ("Vini12cius 000.000.000-00", "Vini12cius 00000000000",…
-
0
votes1
answer765
viewsA: How to open a Userform with the same name as a button? VBA Language
Using the function ShowAnyForm of Cpearson this can be done with a class module. An example with the following VBA project tree: Form An example with the following Form: A form with two buttons with…
-
1
votes1
answer414
viewsA: Increment in String in VBA excel
One solution is the use of Regular Expressions. Regular Expression The following expression may be used: \d+(?=$) Where this expression captures one or more digits \d+ before the end of the string…
-
0
votes1
answer685
viewsA: Block printing via "Print" macro menu
Enter the code in EstaPastaDeTrabalho or ThisWorkbook in English: In which the event Workbook_BeforePrint(Cancel As Boolean) is used. Private Sub Workbook_BeforePrint(Cancel As Boolean) If imprimir…
-
0
votes1
answer157
viewsA: Messagebox in VBA in Excel
Insert Event code Use the event Worksheet_Change, where the data shall be placed within the worksheet in which the data is located. For example, in my case it was in Planilha1: Code for a cell The…
-
1
votes1
answer68
viewsA: Modify Last Selected Button
It will be necessary to use Class modules to accomplish this in a generic way and a form. Form An example with the following Form: In which properties Name and Caption are the same in the image…
vbaanswered danieltakeshi 3,960 -
0
votes1
answer2000
viewsA: Application definition or object definition error
I tested and the code worked for me with a few lines. Then I will list below changes in the code and some observations of what may be happening. Loop If the data is this way: 1 words theme theme2…
excel-vbaanswered danieltakeshi 3,960 -
1
votes1
answer36
viewsA: Orders of the date of a reference in the middle of other dates and references
Using the formula: =SOMARPRODUTO(($A2=$A$2:$A$7)*($C2>$C$2:$C$7))+1 you can sort dates by groups in column A. Where the formula is inserted into cell D2 and then replicated to the other cells…
-
1
votes4
answers13312
viewsA: Find first value greater than a certain number in Excel
The solution works, I do not know if it is with the best Golf Code possible. There is room to decrease this formula. But with the data as in the following picture: Given a gauge and minimum value In…
excelanswered danieltakeshi 3,960 -
0
votes1
answer204
viewsA: "Fade in and Delete - VBA" effect
The problem is that when inserting as Image Shape, changing the transparency is not possible. You need to first insert an Autoshape. Footsteps Create an Autoshape in the Active Worksheet (a…
-
1
votes1
answer68
viewsA: Create a regular expression to validate at least three characters (accepting spaces)
The way @sam presented it works: <form name="ExpressaoRegular"> Por favor, preencha pelo menos 3 caracteres entre letras e números. <input type="text" name="teste" pattern=".{3,}">…
-
0
votes1
answer286
viewsA: VBA Module that stores data from one cell and moves it to another cell and moves to the bottom cell
Sendkeys The method Application.SendKeys "{ENTER}" will only press the key Enter when it is called. Therefore, if your cursor was in the VBA code. It will give the command of New Line in the VBE.…
-
0
votes1
answer48
viewsA: Reset Page every click
The code works the way you are doing, but if you want something more generic create a Class Module or do it the following way: Since I don’t know how your entire form structure is and what you…
-
1
votes3
answers3521
viewsA: Failure of RANGE class SELECT method
Event Worksheet_activate To use this event without looping in, you cannot use the .Select within the event. In fact, .Select/. Activate/. Activecell should be avoided almost always (except to…
-
0
votes1
answer669
viewsQ: Button Bind in Tkinter.ttk Treeview in another Class
Problem How to create a button bind class in ttk. Treeview? Sample Code When programming the main class, which defines Treeview and then create another class that defines bindings and events. It is…
-
0
votes1
answer116
viewsA: How to group data and list in another excel table
Table Dynamics This can be done with a dynamic table. In Inserir > Tabela Dinâmica, as shown below: Upshot <table><tbody><tr><th>Rótulos de Linha</th><th>Soma…
excelanswered danieltakeshi 3,960 -
1
votes2
answers217
viewsA: Default list replacement in regex
edit the original string by removing duplicate spaces, add a comma before the date, remove spaces in the hyphen, and the dot end of all strings. To solve with Regular Expressions, the deletion of…
-
1
votes1
answer893
viewsA: Separate numbers and words
Isnumeric function The function Isnumeric() is being used. Where the description is: Returns a Boolean value that indicates whether an expression can be evaluated as a number. Therefore, it only…
excel-vbaanswered danieltakeshi 3,960 -
-1
votes1
answer337
viewsQ: Increase Spirograph Loop Speed in Turtle
Using these references: Spirograph Patterns and python 3.4 Spirograph error the following code has been created: Code import turtle import math import random def Xcord(R,r,p,t): return (R-r) *…
-
2
votes5
answers286
viewsA: Regex to capture dimensions of a product with unit of measure
Regex With the following regular expression: ([\d,]+)[\s\D]* it is possible to capture each given value. And with the regular expression ([\d,]+)[\s\D]*([\d,]+)[\s\D]*([\d,]+)[\s\D]* and the demo,…
-
0
votes1
answer226
viewsA: insert another form value automatically into the active textbox
There are several ways to communicate between two forms, a simple way to verify this is by performing the following. Example Two forms are created, the Userform1 and the Userform2 Userform1 The…
vbaanswered danieltakeshi 3,960 -
1
votes1
answer1378
viewsA: Filter dynamic table by Listbox
Advanced Filter It is possible to perform an advanced filter with Soen’s Ralph code, remove the accent from the word with the Extendoffice code and ignore lowercase or uppercase by capitalizing the…
-
1
votes1
answer4929
viewsA: Filter dynamic table by word in a cell
As stated in the comment, the desired code is the from this Soen link Sample Data For this Example Table: Upshot This is the expected result: Code Option Explicit Sub FilterCstomers()…
-
0
votes1
answer203
viewsA: Problem Set Invokepattern = Button.Getcurrentpattern(Uia_invokepatternid) - vba
Reference The Uiautomationclient reference should be added to the VBA project. Code 'x64 Public Sub AddReference64() ThisWorkbook.VBProject.References.AddFromFile…
vbaanswered danieltakeshi 3,960 -
0
votes1
answer927
viewsA: Identify the ID/Number of each Text Box Shape
Text Boxes If you want to get only from text boxes as from the image below: The estate .HasTextFrame can be used in a loop in all presentations and shapes. Code Dim sld As Slide Dim shp As Shape For…
-
0
votes1
answer160
viewsA: Show diluted value according to the last day of the month! Excel
Edit =SE($F2<>"Não";($F2/(DIA(DATA(ANO(HOJE());MÊS(HOJE())+1;0))))*DIA(HOJE());"") Explanation If column F is different from string "No", divide the value of F2 by the number of days of the…
excelanswered danieltakeshi 3,960 -
0
votes2
answers134
viewsA: REGEX - Small details that don’t match
Regex This regular expression solves the question of example: (?:[a-z]\))?([\s\S]+?)(?:<br\s*\/>\s*)?(?=[a-z]{1}\)|$) Where group 1 of each match is captured without the starting letters of…
regexanswered danieltakeshi 3,960 -
0
votes1
answer95
viewsA: Year/Month/Day... how to do
Follow a demo code of how to generate the month and day for a specific year. I suggest running step-by-step code to understand it (with F8 key). Code Dim Ano As Date, Data As Date Dim i As Long,…
vbaanswered danieltakeshi 3,960 -
3
votes1
answer8769
viewsA: Calling a macro in different modules
Example A simple example will be demonstrated, with the following tree in VBA: So, assume that all the programming is in the same Workbook. In Module 1 there will be the Sub executar() In Module 2…
vbaanswered danieltakeshi 3,960 -
0
votes1
answer130
viewsA: Delete unwanted VBA values
Code Dim ws3 As Worksheet Dim UltimaLinhaE As Long Dim RangeVisivel As Range Set ws3 = ActiveWorkbook.Worksheets("Planilha3") With ws3 'Limpa os Autofiltros da Planilha para evitar erros If…
vbaanswered danieltakeshi 3,960 -
1
votes1
answer1985
viewsA: Auto VBA Filter - excel
Code Sub filtrar() Dim ws3 As Worksheet Dim UltimaLinhaE As Long Worksheets(Array("Planilha1", "Planilha2", "Planilha3")).Copy Set ws3 = ActiveWorkbook.Worksheets("Planilha3") With ws3 'Limpa os…
vbaanswered danieltakeshi 3,960 -
0
votes1
answer1025
viewsA: How to Load a listview with more than 30 columns?
Sample data With the program of Excelmaniacos, a test was performed with data from column A to AN, from row 7 to 80. As in the table below:…
-
1
votes1
answer955
viewsA: Generate a file and attach in the email with vba
Code Sub MandaEmail() Dim EnviarPara As String Dim Mensagem As String, caminho As String, Texto As String Dim wb As Workbook 'Criar Arquivo caminho = ThisWorkbook.Path & "\" & "temp.xlsx"…
vbaanswered danieltakeshi 3,960 -
1
votes1
answer42
viewsA: Error #VALUE! when using IF operation
You can settle this with: =SEERRO(SE(E(LOCALIZAR("qwe*";B:B);LOCALIZAR("qaz";C:C));VERDADEIRO;FALSO);FALSO) That uses the function SEERRO() OR IFERROR() to return the false value. This is because by…
excelanswered danieltakeshi 3,960 -
1
votes1
answer266
viewsA: Error at runtime '13'
You can use the function Integrity() Code 'percorre da linha 2 até a última preenchida em A For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'se em B for #N/D If WorksheetFunction.IsNA(Cells(i, 2))…
vbaanswered danieltakeshi 3,960 -
0
votes1
answer1044
viewsA: VBA - Select a file to attach in the email
Autoit The solution found uses the Autoit script externally to the VBA, because the VBA is locking as soon as the upload window is opened and only restarts the code when it is closed. The same…
-
0
votes1
answer974
viewsA: VBA code to insert images in excel cannot recognize images that have letters and special characters in the image name
The problem with your role is that when you insert: =getImage(SP20-TP02) Excel converts SP20-TP02 to SP20-TP2 and understands as a range of cells from column SP and row 20 to column TP row 2, which…
-
2
votes1
answer5260
viewsA: VBA code to delete line
I can think of three ways to accomplish this action: Excel functions for string manipulation InStr() and Len() Wildcards Regex And different ways, some more optimized to delete Excel functions A…
-
0
votes1
answer44
viewsA: Skip digits when running
You used this answer: /a/262274/75104, then for more information about the code, refer to the answer given. Regex A new Regex is required, in which the demo of Regex101 can be seen here. Regex:…
-
2
votes1
answer526
viewsA: How to "concatenate" multiple values entered by a user in the table in an Msg box?
Forms From what I understand you will have two forms Userform1 and the Userform2 In Userform1 there is a button that you click to exit. In Userform2 the message will be displayed. Therefore, for the…
-
1
votes1
answer223
viewsA: VBA - Form that feeds a spreadsheet with values
You own a typo error (typing error) on the line: lin = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row The right thing would be: lin = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row That…
vbaanswered danieltakeshi 3,960 -
2
votes2
answers623
viewsA: Regular expression for dynamic URL
Regex This would be the Regex: ((?:https|http|ftp)?:\/\/)?([^\/,\s]+\.[^\/,\s]+?)(?=\/|,|\s|$|\?|#)(.*) In which the demo on Regex101 can be seen more didactically. Code Example of the Regex101…
-
6
votes1
answer6469
viewsA: Macro email range + signature
#Code Sub Envia_Email() 'Seleciona o intervalo de células na planilha ativa. Application.DisplayAlerts = False 'desabilite o alerta Sheets("Base filtrada").Select Dim email_envio As Variant…
-
3
votes2
answers648
viewsA: Ignore scoring in a sequence of numbers using Regex with Java
Regex The Regex: \b(\d+)(\.|,|\b) Upshot With these test strings: Fatura Cliente: 6.823935.10 Fatura Cliente: 6,823935,10 Fatura, Cliente: 6,823935.10 Fatura. Cl1ente: 6.823935,10 And replace it…
-
1
votes1
answer2800
viewsA: Macro to automatically search images and play in excel
Code Follow the example code to perform this. The explanation is as a comment in the code. Dim ref As String, codImg As String, caminho As String, caminhoImg As String Dim corRng As Range Dim ws As…
-
2
votes1
answer104
viewsA: riding a Regex
Regex: This is the Regex: (a)(l)(t)(e)(r)(a)(c)(ao|oes)( )?(de)?( )(c)(a)(d)(a)(s)(t)(r)(o|os)( )?(-)?( )(s)(o)(c)(i)(a)(l|is) And the demo no regexstorm. Explanation (a)(l)(t)(e)(r)(a)(c) -…
-
2
votes1
answer275
viewsA: Problems in VBA code execution
Code You need to trigger the onchange event with the following code 'https://stackoverflow.com/questions/49537537/ie-click-not-working-properly-due-to-value-onchange If el.Name = "leg_campo1" Then…