Posts by Evert • 1,980 points
85 posts
-
2
votes1
answer84
viewsA: How do I make a customer list appear in a search from the name in Excel?
Depending on the context in which your table is. I suggest using Excel tables to facilitate data entry: If you have doubt how to create on youtube have several tutorials for this or click here After…
-
1
votes3
answers1724
viewsA: how to close Word opened by VBA
Use the option below: objDoc.Quit Instead of ". Close" Before "Quit" save what you need to save. Issue 1 Example Sub abreWord() Dim objDoc As Object Set objDoc = CreateObject("Word.Application")…
-
1
votes2
answers2874
viewsA: Separate fixed headphones from furniture to add ninth digit
The old cell phone also has 8 digits, so it is having this difficulty. Do it this way: =SE(F1<55555555;D4&" "&E4;D4&" 9"&E4) F1 being the cell phone number. I suggest however,…
-
0
votes1
answer175
viewsA: Activate found cell
I believe you are trying to activate a cell in a spreadsheet that is hidden or not active. Try to activate the spreadsheet: '[...] ' Ativar a Planilha: Worksheets("Central.de.Clientes").Activate…
-
0
votes1
answer190
viewsA: Write dates (month/year) between two dates
Try using the function below to create the desired structure for the first line and then adapt the code to run with all lines of your worksheet: Sub Escrever_Data() Dim DATA1 As String Dim DATA2 As…
-
1
votes1
answer69
viewsA: formatting according to cell content
See if I can understand and try the following formula in column’D' (cell D1):…
-
2
votes3
answers4302
viewsA: Picking tabs name in a closed spreadsheet
I always suggest that when referring to an external spreadsheet use the CodeName instead of Name, that the user can easily change. Then you will have control of where you are looking for the data…
-
1
votes1
answer100
viewsA: Excel cannot read cell in VB code
To read the cell value you must use .value or .text if applicable. Using .FormulaR1C1 the result will be the cell formula. Follow correction below: verificaCel = x.ActiveCell.Value…
-
2
votes1
answer3070
viewsA: VBA+Excel. Code error (incompatible Byref argument type) in Function
To fix this error just use ByVal in the declaration of variables in their function, but there are two other points that I believe need to correct in your code, follow corrections: Dim a, b, c,…
-
1
votes1
answer604
viewsA: PROCV Excel - Use of connections
Since you’re using connections, I suggest you use the Microsoft Query, as follows: I have a spreadsheet (.xlsx data) with your data, example: In your result sheet (result.xlsx), where you want your…
-
1
votes1
answer288
viewsA: Reset the counter every time you change line
To reset the counter put the code contLinha1 = 0 right after the first loop: [...] While Range("a" & cont).Value <> "" ' Aqui! contLinha1 = 0 coluna = 1 While coluna <= 32 valor =…
-
1
votes2
answers2872
viewsA: How to read a single-line txt file in VBA?
The best I could get was this way: Sub Buscar_Primeira_Linha() Dim LINHA As Integer ' Configura o número da linha que deseja LINHA = 1 ' Primeira Linha, por exemplo Ler_Arquivo "C:\Arquivo.txt",…
-
3
votes2
answers421
viewsA: How to expand range of numbers with data?
My suggestion is this:: Create a code to fetch cell numbers, as follows:: Function Extrair_Numero(ByRef TEXTO As String, _ Optional ByRef SEQUENCIAL As Integer = 1) As Double Dim i As Integer Dim…
-
2
votes4
answers33862
viewsA: How to search for values in column A that do not exist in column B?
There are several ways to do what you asked, one of them would be to highlight the cell for a quick view. My suggestion is to use conditional formatting as follows: Select column "A" and click and…
-
1
votes2
answers938
viewsA: Excel form - incompatible types
When using .Cells only rows and columns should be used numerically and using with always leave the commands on separate lines as below. Another correction in your formula is that you are using the…
-
1
votes2
answers422
viewsA: Loop to Repeat Names
You’d need to step up your loop with some information, follows a small model I made (I could not test...) but to see the logic: Dim i As Integer Dim j As Integer Dim wsORIGEM As Worksheet Dim…
-
1
votes2
answers2756
viewsA: Checking and Filling Excel Cells
Hello! I think you don’t need VBA for this, you can use condicinal formatting as follows: Select all cells you want to color, go to conditional formatting and click New Rule Select the last option…
-
3
votes2
answers910
viewsA: How to call a variable from others?
I believe that there are several options to ask what question, I would suggest using a Array to that end, as follows: Dim CAMPO As Control Dim DADOS As Variant Dim i As Integer ' Configura seu array…
-
0
votes1
answer18482
viewsA: Compare two columns in EXCEL and if they are equal return a certain value
In this case you do not need to do in VBA, you can use the PROCV as follows: In the cell C2: =PROCV($A2;$H$2:$I$6000;2;FALSO) In the example above I put as having around 6,000 municipalities, but…
-
0
votes1
answer74
viewsA: Create image in another form
Just change your code here: Set pic = Plan2.Pictures.Insert(sURL + sParameters) Or Set pic = Sheets("Plan2").Pictures.Insert(sURL + sParameters) Now.. you could also dynamically place within your…
-
1
votes1
answer1369
viewsA: Fill the formula horizontally but increment the cell references vertically
You can use the function TRANSPOR that has a little secret to work. In the following data: Select where you want to have the data transposed (linked) and type the following formula: =TRANSPOR(A1:A4)…
-
1
votes1
answer1556
viewsA: Problem with waterfall drop-down list
Right! One of the solutions I use for this conditional selection would be so: I create two tables with my data using the Pre-formatted Excel table, something like this: Of course there is the…
-
1
votes2
answers6025
viewsA: Add blank Excel line using VBA
See if this way suits you: Sub AdicionaLinhaBranco() Dim COLUNA_VERIFICAR As String Dim ULTIMA_LINHA As Integer Dim QTD_LINHAS As Integer Dim i As Integer Application.ScreenUpdating = False…
-
2
votes1
answer1019
viewsA: Rename PDF File
See if the way you are, I made small adjustments in your formula where I put comment. To list the files of a particular folder in column "A": Sub listarArquivos() Dim MyFolder As String Dim MyFile…
-
3
votes1
answer1679
viewsA: Which excel function should I use?
One of the ways to solve this would be the following code (vba) in your spreadsheet (Worksheet_Change): Private Sub Worksheet_Change(ByVal Target As Range) Dim celNome As String ' Célula onde deseja…
-
1
votes2
answers11636
viewsQ: Line breaking in Excel (2013) via formula
How to break line in formula in Excel 2013 (above)? I used to do it this way: ="linha1"&CARACT(13)&CARACT(10)&"linha2" Current result: linha1linha2 Expected result: linha1 linha2…
-
0
votes2
answers1037
viewsA: Move only part of a text to another column
It all depends on how standardized your data is, but if the numbers are always in the last positions as in your example: AV NOSSA SENHORA DE COPACABANA 664 AV DOM SEVERINO AV DOM HELDER CAMARA, 5332…
-
2
votes1
answer482
viewsA: How to delete or hide the password entry to access the data of a server with SQL Server from the VBA or Excel macro?
Friend see if the function below suits you: Sub SQLConnect(servername As String, dbname As String, tablename As String, uname As String, pword As String, sheetname as String)…
-
3
votes1
answer475
viewsA: Advanced search in Laravel 5
The problem lies in the amount of argument sent in the clause when(), which currently accepts only one. A suggestion would be to break in two, as follows: $exames =…
-
1
votes2
answers40
viewsA: How to sort the position of a matrix automatically?
Just create the array again: $newArray = array_values($oldArray);
-
0
votes2
answers508
viewsA: Search in Table of Values in excel
I suggest using PROCV(), thus: =PROCV(VALOR_PROCURADO;MATRIZ;COLUNA_RESULTADO;FALSO) PHONY at the end is to bring the result of the exact search. Another thing that can help you a lot is to use…
-
2
votes1
answer51
viewsA: Excel SES returning #N/D
The error is in the way you are testing with the SES(): =SES(FRETES!$I$3>1<30.... The correct formatting of SES() would be:…
-
1
votes2
answers224
viewsA: Check the end of the song and run macro
One of the ways would be to take the duration time of the mp3 file that will be played and put Excel in pause during this time for when to finish, return the button to the default color. To get the…
-
1
votes1
answer1136
viewsA: Count values if values in two columns of the same row are specified
In your Result Sheet put as follows in B2: =CONT.SES(Historico!B:B;$A1;Historico!C:C;B$1) Drag to B2 for D2and see if it works, then drag to other suppliers adapting the formula.…
-
1
votes1
answer4844
viewsA: How to change the icon of the Excel document
To change the icon just create a shortcut (e.g. desktop), click file properties (shortcut) and click the button Alterar Ícone and choose the desired icon. Another option would be to create a file…
-
2
votes1
answer282
viewsA: Migrations Laravel 5.4
In Laravel 5.5 we will have a function called: php artisan migrate:fresh That will make a drop in the tables and will start new migration. This error happens because the command refresh tries to…
-
3
votes1
answer328
viewsA: Check module name via VBA
See if the function below helps you, as it lists all modules per spreadsheet: Sub BuscaModulos() Dim modName As String Dim wb As Workbook Dim l As Long Set wb = ThisWorkbook For l = 1 To…
-
0
votes2
answers1586
viewsA: Apply conditional formatting individually to each sheet line
Just like almost all solutions in Excel, I believe there are several ways to do this. By your comment, you want to check on the same date, in the case on the same line if there is repeated city and…
-
1
votes1
answer913
viewsA: Export tabulated text in Excel
In fact there is a limitation in Excel up to 240 for columns, but Microsoft itself gives us a light. Below is a model adapted from Microsoft to export all data from the active spreadsheet to a text…
-
2
votes1
answer2153
viewsA: A Macro to run all Modules?
All the modules, functions and subs are interconnected in Excel, could have each sub independent and have another function to run everything in sequence, thus: Sub um() 'código [...] End Sub Sub…
-
0
votes1
answer2071
viewsA: How to search in a column by value =< 200 (run command)?
To make a loop in a column can be done as follows: Dim rng as Range Set rng = Selection ' ou Range("A1:A30") ou Range("P:P") no seu caso... For Each Row In rng.Rows ' Seu código aqui... ' If…
-
2
votes2
answers2083
viewsA: Block cell filled with VBA
To block a cell the worksheet must be protected. Column 3 must have the cells unlocked for editing according to your code and enter the following: Protect: ActiveSheet.Protect DrawingObjects:=False,…
-
0
votes1
answer62
viewsA: How to convert Pounds to Euros VBA?
To change these settings in VBA use the function below, changing as you wish: Sub mudaSeparador() Range("A1").Formula = "1,234,567.89" ' Defina os separadores que deseja Application.DecimalSeparator…
-
1
votes1
answer173
viewsA: Summarize data in Excel without VBA or dynamic table
Use a somases to sum all the sold values of a given product. https://support.office.com/pt-br/article/SOMASES-Fun%C3%A7%C3%A3o-SOMASES-c9e748f5-7ea7-455d-9406-611cebce642b =SOMASES(B2:B9;A2:A9;"P1")…
-
0
votes1
answer158
viewsA: How to create Scope in Laravel 5.4 with linked tables via belongsToMany
I got it with the help of our community as follows: public function scopeUn($query, $unidade_id) { $query->whereHas('unidades', function ($query) use ($unidade_id) {…
-
0
votes1
answer458
views -
0
votes1
answer158
viewsQ: How to create Scope in Laravel 5.4 with linked tables via belongsToMany
The structure of the table is this: pessoa id - integer nome - string unidade id - integer nome - string pessoa_unidade pessoa_id - integer unidade_id - integer I have the models: class Pessoa…
-
0
votes1
answer1326
viewsA: Excel - Tables - Identifying the Next Row
Welcome! If you are looking for how to add a row to an object as a predefined table the code will look something like this: Set myNewRow = ActiveWorkbook.Worksheets(1).ListObject(1).ListRows.Add In…
-
1
votes1
answer292
viewsA: Error sending files to aws S3 server in Laravel
When using flysystem, avoid using the domain localhost, create a hostname for the local machine, as below and should work: http://local.dev
-
0
votes3
answers1307
viewsA: Count elements between two quantities
Right, as there is text in your formula, ideal will be to create a new column with only the numbers (ages) to create the desired frame. From there use the following formulas:…