Tag Info

Hot answers tagged

4

To open the VBA editor in any Microsoft Office product, press Alt+F11. Next, you need to determine how you want your VBA to be implemented. Do you want it to be triggered by some event in the software, such as the file being saved or the selection on the spreadsheet being changed? Or do you want to be able to execute the code yourself whenever you like? If ...


2

This creates a folder structure with VBA. Short and beautiful. Sub CreateFolderStructure() For Each objRow In UsedRange.Rows strFolders = "C:\myRootFolder" For Each objCell In objRow.Cells strFolders = strFolders & "\" & objCell Next Shell ("cmd /c md " & Chr(34) & ...


2

The quickest way would be to write a macro in VBA Excel that opens the csv files, changes the formatting and saves it as csv file again. Try the following course of action: Record a macro in Excel that does exactly this task for one csv file (start recording, open the file, do the formatting and save the file again). Use this answer of SU to traverse ...


2

The function NETWORKDAYS can calculate the number of working days between two dates. To convert from days to hours, we need to multiply that by 8. Additional, you need to calculate the number of hours for the first day (the difference between 17 hour, or 17/24-part of the day) and the last day (the difference between the finishing time and 8 o'clock, or ...


2

Your code monitors column A, cells A2 to A10, not column E. It places the timestamp with an offset of 1 into the respective cells in column B. If you want to monitor column E and enter the time stamp in column G then change the code to Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub ...


2

These steps are done using Office 2010 (except where noted otherwise) but should be similar enough for 2007 and 2013 as well. I'm doing it with Excel but the differences should be subtle. The first step is to add the Developer tab to the ribbon. In Office 2007, Click “Office Menu” -> “(Program) Options”.  (“Office Menu” refers to the button in the top ...


2

You were so close! All you need to do is to remove the trailing underscore and comma from the end of the line myAttachments.Add "C:\Test.txt", _ It should simply be myAttachments.Add "C:\Test.txt" I tried your code with that change, and it worked just fine.


1

Try this [EDITED]: Dim arr() As Variant ' let brackets empty, not Dim arr(1) As Variant ! For Each a In range.Cells ' change / adjust the size of array ReDim Preserve arr(1 To UBound(arr) + 1) As Variant ' add value on the end of the array arr (UBound(arr)) = a.value Next


1

If your range is a single vector, and, if in a column, the number of rows is less than 16,384, you can use the following code: Option Explicit Public Function toArray(RNG As Range) Dim arr As Variant arr = RNG With WorksheetFunction If UBound(arr, 2) > 1 Then toArray = Join((.Index(arr, 1, 0)), ",") Else toArray = ...


1

Try this macro replaced AnotherSheet with the real name of it: Sub MyMacro() ' Part 1 - first move the range to another sheet Range("A5:A20").Select Selection.Copy Sheets("**AnotherSheet**").Select ActiveSheet.Paste ' Part 2 - move the cell to right place Dim r As Range For Each r In Selection r.Cut r.Offset(r.Row * ...


1

You can use indirect to reference a named range dynamically. If your =sumifs() formula is in Column D of your Table 2 (I'm assuming) then =SUMIFS(Client_Amount,Broker_Name,A2,Broker_Market,B2,INDIRECT("Client_" & C2),C2) should do the trick.


1

Here's a PowerShell script to update several CSV files from a specific folder in one step. ForEach ($file in Get-ChildItem "C:\myFolder" -filter "*.csv"){ Get-Content $file.Fullname | ForEach { $arLine = $_ -split "," $date = [DateTime]$arLine[2] $arLine[2] = $date.ToString(" dd-MMM-yyyy HH:mm:ss") ...


1

I'm not sure if this is the same thing you guys have been looking for, but I found I was able to access VBA in Office365 by opening Excel, going to FILE > OPTIONS, select 'Customize Ribbon', add 'Developer' to show in the ribbon. Now simply go to the Developer tab and click 'Visual Basic', which will pop open a new window.


1

I guess one of the easiest ways to do this would be installing a SSH client on Windows and then using a SSH connection to trigger the script on the Linux side. The command to call on the Windows side could be as easy as something like this: ssh.exe user@hostname /path/to/the/script Note that your ssh client will need some way to directly authenticate ...


1

I don't doubt the truth of this sentence but I struggle to follow the logic: "This is mission-critical software, and its way too complex for any sane person to try and touch it.". Anyway ... I would try to separate this into two Excel files. One would gather the data using the VBA plugin, and would only be updated on machines with "terminal access". ...


1

This does exactly what you want. Option Explicit Sub DoTheThing() Dim row As Integer row = 1 ' WHAT IS THE STARTING ROW Do While (Range("A" & row).Value <> "") Dim vals() As String vals = Split(Range("B" & row).Value, ",") Dim lookUpValue As String lookUpValue = Range("A" & row).Value Dim result As String ...


1

The way I would go about it would be to start by breaking up the problem into a bunch of columns, each with a piece of the problem. For example: A B C D E F G H I J K L M N 1 12 0,12,13,14 2 5 8 0 12 13 14 Yes Yes No No Yes, Yes, No, No 2 101 101,102,103,104 4 8 12 101 102 103 ...


1

There are a few ways to do this, but the easiest is to use Application.Run(). As an example, I have a workbook named "SC Fun.xlsm" in order to run my getData() subroutine in that workbook, from another workbook, I can write: Sub runAnotherWokbooksMacro() Application.Run "'SC Fun.xlsm'!getData" End Sub


1

There is no OnMouseOver in Excel VBA. I do not believe it can be done.


1

Whilst there is no formal OnMouseOver event, you can put together a VBA hack to get around it. It involves using the HYPERLINK function. There's more detail here, but in summary: If you create a new VBA module and then add a User Defined Function to do what you want: Public Function OnMouseOver() Sheet1.Range("A2").Value = "You hovered over a cell" End ...



Only top voted, non community-wiki answers of a minimum length are eligible