Posts by danieltakeshi • 3,960 points
212 posts
-
1
votes1
answer445
viewsA: VBA - Error: The 'frames' method of the 'Jscripttypeinfo' object failed
EDIT: This code works the same way as the other code, but first click on the Legislation tab. I suggest using the code that generates txt with html to test this application. And learn about DOM. Sub…
-
1
votes1
answer1216
viewsA: To rename a folder added the current date to the name
You are in error because the String Data returns the value: 03/23/2018. When you try to change a folder name manually and try to insert /, the following message appears: Therefore you must replace…
-
1
votes1
answer1517
viewsA: VBA - Popular array string and do not repeat values
Problem With the data of Birds in the spreadsheet "Birds" +----------+ | Eagle | | Penguin | | Heron | | Flamingo | | Toucan | | Penguin | | Heron | | Eagle | | Flamingo | | Flamingo | | Heron | |…
-
1
votes1
answer4893
viewsA: Handling of internet explorer using VBA
Code Test this code that will save the file in the same directory as the Excel file. 'Declara função Sleep #If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As…
-
3
votes1
answer2678
viewsA: OPERATION OF THE EXPLORER VIA VBA
Code You can accomplish this with this code: 'Declara função Sleep #If VBA7 Then Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems #Else Public…
-
0
votes2
answers1488
viewsA: Chronologically order VBA date and time
Example: With this test data: +------------+-------+ | 28/02/2012 | 10:00 | | 19/02/2015 | 09:00 | | 22/02/2015 | 04:32 | | 01/03/2017 | 08:00 | | 19/01/2018 | 01:00 | | 19/01/2018 | 15:00 | |…
-
2
votes1
answer8865
viewsA: Find last filled cell in a range
Last Cell of a range Dim Calc As Worksheet Dim intervalo As Range Dim matriz As Variant Dim i As Long, j As Long Set Calc = ThisWorkbook.Worksheets("Cálculos") Set intervalo = Calc.Range("A20:C30")…
-
0
votes1
answer767
viewsA: how to make a graph in vba excel in the form of goals?
VBA A simple code to be used is this, where the chart must be selected and then the code is executed: Sub ColorByValue() 'https://peltiertech.com/vba-conditional-formatting-of-charts-by-value/…
-
0
votes1
answer1630
viewsA: Vba + Google Chrome
Unfortunately VBA can not interact with these software natively, only with Internet Explorer (IE). Therefore, I recommend using a 3rd party: Selenium VBA…
-
0
votes2
answers3532
viewsA: Copy rows from a table that match one criterion and paste into another Excel sheet
One way is to filter in coluna Z table by CLOSED and copy the entire visible area and then clean it. So it’s more optimized than iterating one by one. Dim a3 As Worksheet, FECHADA As Worksheet Set…
-
0
votes1
answer1003
viewsA: How to close an alert in IE in with VBA
I already searched once and did not find how to hit the error button with the VBA, maybe using the Autoit accomplish this. However, the recommended is the handling of errors, the solution of these…
-
1
votes1
answer1371
viewsA: Extract Data between Lines from txt to VBA spreadsheet
Regex Enable Regex in Excel VBA Regex needs to be enabled, Enable the Developer mode In the 'Developer' tab, click 'Visual Basic' and the VBA window will open. Go to 'Tools' -> 'References...'…
-
2
votes2
answers242
viewsA: Split date format
With the data: +---+---------+-------------+ | | A | B | +---+---------+-------------+ | 1 | 9630638 | 43145,57861 | +---+---------+-------------+ With the formula of concatenating…
-
5
votes1
answer174
viewsA: Is it possible to remove the first sequence of the last letter of the regex?
The Regex used is nt+ or the nt*, with the demo on Regex101. Where the Regex101 site can be used to test Regexes, widely used because of the formatting colors, simple design and ease of use.…
-
2
votes1
answer484
viewsA: Print alternate vba excel columns
Edit: It is not possible to print a non-stop range directly on the same sheet. Then a temporary spreadsheet is created to store the data in order to print them. Note: This will work only if all…
excel-vbaanswered danieltakeshi 3,960 -
0
votes1
answer620
viewsA: Code VBA button
To solve your problem, just use the Hyperlink.Follow in the desired cell: ActiveSheet.Range("b2").Hyperlinks(1).Follow So to find the hyperlink, the method .Find is used. Refer to this answer for…
excel-vbaanswered danieltakeshi 3,960 -
2
votes1
answer1729
viewsA: Find and Copy sheet line to form, change fields and write to the same sheet with new ID
Example As no example was defined, the following data were used for the tests: This is an example and you should change for your application. Locate To locate a String in Excel there are numerous…
-
1
votes3
answers1724
viewsA: how to close Word opened by VBA
Quit and Set Word object = Nothing You create two objDoc with Set objDoc. Then at the end you close only the last, while the first remains open. A VBA program to close all Word files is as follows:…
-
1
votes2
answers1235
viewsA: Identify the position of the first empty cell
To obtain the number of last row of column B, i.e., return the value of the last row filled in column B. The following formula is used: =SEERRO(PROC(2;1/(B:B<>"");LIN(B:B));1) So the final…
excelanswered danieltakeshi 3,960 -
1
votes1
answer404
viewsA: Remove duplicate names by VBA approach
1st Option - Fuzzy Lookup Add-In for Excel You can use the Fuzzy Lookup Add-In for Excel or create Fuzzy logic itself in a program. Search for Fuzzy Search or in English Fuzzy search/lookup. Fuzzy…
vbaanswered danieltakeshi 3,960 -
1
votes3
answers2884
viewsA: REGEX - Capitalized words in the middle of the sentence
You can use this Regex: (?<!\.)(?:\s([A-Z\u00C0-\u00dd][A-Z\u00C0-\u00dd]*[a-z\u00E0-\u00ff][a-zA-Z\u00C0-\u00ffA-Z]*)|\s(A|O|À)(?=\s|\.)) or…
-
2
votes1
answer614
viewsA: VBA events in Powerpoint
Event To trigger some programming when the Powerpoint Slide reaches/reaches a particular page during the presentation, the event OnSlideShowPageChange can be used. The code will be called before…
-
1
votes1
answer340
viewsA: Save Chart of a spreadsheet in a folder by VB
Save Graphics as PNG To save all charts use this code: For Each sht In ActiveWorkbook.Sheets x = 1 For Each co In sht.ChartObjects co.Chart.Export SeuDiretório & "\" & sht.Name _ & "_"…
-
0
votes1
answer3103
viewsA: Excel - VBA sum +1 to line
EDIT: Regex Enable Regex in Excel Regex needs to be enabled, Enable the Developer mode In the 'Developer' tab, click 'Visual Basic' and the VBA window will open. Go to 'Tools' -> 'References...'…
vbaanswered danieltakeshi 3,960 -
0
votes2
answers902
viewsA: Excel form (Block fields)
This is possible with the use of VBA. After inserting the Optionbutton1, Optionbutton2 and Optionbutton3 option buttons. The following code can be inserted in the form: Private Sub…
-
0
votes1
answer200
viewsA: VBA Error when displaying data from a Worksheet
The Vlookup function is not finding value, so the error occurs. There are some ways to deal with the error, because if you use the function in the spreadsheet the #N/A error will appear. Sub Busca()…
-
1
votes2
answers1066
viewsA: Excel: Transpose 5 to 5 cells
Solution Dynamic transposition should be used, you can transpose manually or with a function Transpose. In this example will be done one by one. Code Create an array with the data column and then…
-
1
votes1
answer3776
viewsA: Excel: How to make a time series using dynamic table and graph with accumulation of values in the row?
Solution You can use the Cascade chart to view the accumulated values. Footsteps 1. Select the data: Select the data to be viewed on the accumulated chart. 2. Insert graph: Enter the chart in…
excelanswered danieltakeshi 3,960 -
0
votes2
answers2874
viewsA: Separate fixed headphones from furniture to add ninth digit
Solution You can use Regex as a VBA function to validate this. Regex Enable Regex in Excel Regex needs to be enabled, Enable the Developer mode In the 'Developer' tab, click 'Visual Basic' and the…
-
0
votes2
answers193
viewsA: Block of txt with Regex
As Flavor was not specified, Excel VBA was used, that uses javascript. Regex Enable Regex in Excel Regex needs to be enabled, Enable the Developer mode In the 'Developer' tab, click 'Visual Basic'…
regexanswered danieltakeshi 3,960 -
2
votes1
answer152
viewsA: VBA - Maximum character in dropdown-list
Referring to this Global OS reply: Maximum drop-down list/formula length in Excel The step-by-step will be explained below: Create Spreadsheet with Validation Data First a spreadsheet should be…
-
2
votes1
answer1532
viewsA: Copy and paste cell when changed
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
votes2
answers2124
viewsA: Take values from an excel spreadsheet and display in a pop-up
Code There are several ways to match the values of an Excel file. (. Find, Match, Arrays, Dictionary, Collection, Autofilter, Loop, Excel Formula) If the spreadsheet has a lot of data the methods…
-
0
votes1
answer2583
viewsA: Collect HTML tag data with VBA
The Whatsapp does not run with the Internetexplorer, therefore use Set IE = CreateObject("internetexplorer.application") will not work because when opening the page in IE, the following screen…
-
1
votes1
answer1262
viewsA: Insert comment via Function
Code of the UDF This is the code of the function to add comment and optionally, calculates something in the cell. Option Explicit Public Function AdicionarComentario(celula As Range, Optional Calcs)…
-
1
votes1
answer2559
viewsA: Limit characters and character type
Test data Using the following test data: Insert Event code Use the event Worksheet_Change, where the data is to be placed within the spreadsheet (MARKET). For example, in my case it was in…
-
1
votes1
answer4263
viewsA: How to copy and paste as image with VBA?
Try this code, in this case it will copy the index chart 1. If there are more charts, change the index number of ChartObjects. The Copypicture method will be used, it occurs that you need to copy…
-
1
votes1
answer9195
viewsA: Formatting to change Color and Status depending on another cell’s Conditional
You can use conditional formatting to format the colors of these two cells based on values. Conditional Formatting Open the conditional formatting Select the two cells that will be filled with…
-
2
votes1
answer137
viewsA: Image with time interval - Excel
Scheduler Use this VBA code to schedule tasks at the desired time: Private Sub Workbook_Open() '-- Roda os Subs ou funções no horário agendado. 'Deve ser colodado em EstaPastadeTrabalho…
-
1
votes2
answers68
viewsA: excell’s formula
If I understand correctly, you want to know in percentage the note according to the range (span). This is widely used in instrumentation, where there are several different values, with different…
excelanswered danieltakeshi 3,960 -
0
votes2
answers127
viewsA: Convert HTML5 table + images to CSV or SQL
Excel VBA To accomplish this in Excel VBA with Regex. Regex The code is: (?:<a.*?>\s*[<img src="]*)(.+?)(?="?>?\s*(?:<\/a>|$)) The validation link on Regex101 and the Debuggex…
-
2
votes1
answer345
viewsA: Check exact sum in a cell from a range
Excel Solver To perform these tasks without using VBA, Solver needs to be used and a simple binary logic is used. Enable Solver in Excel The version used is Excel 2010. Enter the Office or File…
excelanswered danieltakeshi 3,960 -
2
votes3
answers4302
viewsA: Picking tabs name in a closed spreadsheet
You can declare Worksheet in two ways that make it possible to rename tabs. Worksheets(1) or Sheets(1), where you use the index number of the spreadsheet, for example: Dim ws As Worksheet: Set ws =…
-
0
votes1
answer956
viewsA: Web data query in Excel
1) Enter the VBA Enable the developer mode Open the VBA or press Alt+F11 2) Add Userform In the Projects window, right-click and Enter a Userform 3) Insert Web Browser Inside Userform, insert…
-
0
votes2
answers139
viewsA: Sort equal rows
It is possible to perform what was requested by Excel formulas. Input Data Upshot How to do? New formulas were inserted in columns F,G,H and I to compare the two columns and return in the same row…
-
2
votes1
answer156
viewsA: Excel days function Dates does not work
Try changing the format of the Date Duration cell to Number, as the number result 30 in Date format are 30 days after the first date that Excel accepts (which is 01/01/1900), so it is day…
-
0
votes2
answers600
viewsA: For nested parallel VBA Excel
A for loop No need Using copy/Paste is slower than simply writing, 100 lines is feasible. But for more than 20000 lines can become slow. Worksheet actions can be achieved only with a for loop,…
excel-vbaanswered danieltakeshi 3,960 -
9
votes5
answers19971
viewsA: Validate regular expression name and surname
Regex This is the Regex (monster): ^(?![ ])(?!.*[…
regexanswered danieltakeshi 3,960 -
3
votes1
answer10576
viewsA: Split text in excel
Use these formulas: =SEERRO(ESQUERDA($A3;LOCALIZAR(" ";$A3;1));$A3) =SEERRO(DIREITA($A3;NÚM.CARACT($A3)-LOCALIZAR(" ";$A3;1));"") Where if there is an error in the left part of the name, the value…
-
0
votes3
answers8269
viewsA: How to add character after last word written on line
Excel Formula With extra reference cell Insert this formula into a cell =MÁXIMO((NÚM.CARACT($A:$A))), for example Z1 and tighten Ctrl+Shift+Enter (remember to select each cell and press the buttons…