Tag Info

New answers tagged

0

Assuming your table is in e.g. A1:D10 (with headers in row 1, so that Course, Completed?, Prerequisites and Available are in A1, B1, C1 and D1 respectively), and also assuming that the entries in the Prerequisites column are each separated by the precise combination of a single comma followed by a single space, then enter this formula in D2: ...


1

There is no way to completely protect an Excel file. Excel sheet protection and even VBA code protection should be regarded as a safeguard against accidental change rather than malicious intent. The internet is full of free and paid tools to support malicious intent. The only way to keep an Excel file safe is not to make it available to others who might ...


2

Put the following formula into cell B1 and drag down. =INDIRECT("A"&2^(ROW(A1))) Bear in mind, this will give you a #REF! error from row 21 onwards, as per Mike Fitzpatrick's explanation.


0

If I understand you correctly you want to take the sum of values in a row, say from A1:Ax where x = 2^i. You can do that without VBA using the INDIRECT function: =SUM(INDIRECT("A1:A"&(2^i))) So substituting i=8 in the above formula will give you the sum of the first 8 rows in column A. The formula can easily be extended to account for ranges starting ...


0

You can do this without VBA. You can use the following formulas for all rows in column B and C of your master sheet; =INDIRECT(" 'sheet " & ROW()-1 & " '! " & " B3 ") =INDIRECT(" 'sheet " & ROW()-1 & " '! " & " B5 ") This is assuming that your sheet names are sheet1, sheet2 and so on. If this is not the case, simply replace 'sheet ...


1

You can use a worksheet change function in vba. Something like Private Sub Worksheet_Change(ByVal target As Range) If Intersect(target, Range("e7:e8")) Is Nothing Then Exit sub Application.enableevents = False ActiveSheet.range("A4").formula="=if(e7="""",e8*765,e7*960)" Application.enableevents = True End Sub Anytime the sheet changes it checks if the ...


0

You can use VB to change any value during calculation stage. But doing so means that the macro will change the value in A4, and you'll have to add something to know that the value should not be updated on a second go. So in theory it is possible, but it is highly impractical to do so. If you purely want to have a dropdown list with popular values, you do ...


0

In business, it is common to want to use a formula to calculate a value, but still allow the user to override that formula for special cases. Say A4 represent Total Cost and contains: =D8*G8 where D8 is the unit cost and G8 is the quantity. But for special customers we want to be able to quote a discounted cost. We put the discounted cost in H8 and ...


2

There is the Revisions Object in VBA. From there it's simple to iterate through each revision item, accept and hightlight it with a predefined color. Additionally we need to temporarly disable tracking. Sub tracked_to_highlighted() tempState = ActiveDocument.TrackRevisions ActiveDocument.TrackRevisions = False For Each Change In ...


3

You need to fill your recordset object (That's the rs variable you declared). I have changed the code below to do just that. You can then copy the results from the recordset directly into a range with RANGE.copyfromrecordset <yourrecordset> below I have added that as well where I dump the results into SHeet1.Range("A1"). You'll probably want to change ...


0

It seems that you have two requests. Formula for highlighting duplicates. Assuming your data is in A1:A1000 put this formula into cell B1 and drag down: =COUNTIF($A$1:$A$1000,$A1) Duplicate values will return a number greater than 1. To filter the values that are duplicates/highlighted. Apply a filter by selecting your data and hitting Data>Filter (or ...


0

This is a pretty basic bare bones VBA setup for this request. I would assume you'd probably want to tweak it some to make it a bit more. It just loops through each Class/Student combination in your sheet and pops up an input box asking for the grade. Sub getGrades() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") 'change this to your ...


-2

I found something about how to copy office theme colors from one workbook to another using VBA and about Excel colors somehow getting changed? ...maybe this can be useful too, it includes VBA code: VBA code for working with Excel 2007 themes Hope it helps :) Have a nice day.


0

The solutionTo automate this, use some VBA code that resizes your table after updating the start and end years. The codeHere is my implementation of this, with the years in "Sheet1" (cells B1 and B2) and the table in "Sheet2". Note that you must add this code to the sheet where the years are in, because the code has to run whenever you change something on ...


0

Assuming it is just one column you want to change, VLookups are the way to go. In your Sheet 1, insert a column to the right of the original price column, and setup a VLookup formula that looks up the price column from Sheet 2 of any duplicate records. Once you have done that, insert another column to the right of the VLookup column with an If function ...


0

One or the other of your routines should invoke PPT and get a PPT object to work with, not both. One thing I'd be immediately suspicious of is that the cleanup routine in copy_chart is closing the PPT app object, so when control returns to the calling subroutine, the object is null. If the error occurs on the "PPT.Save" line, I'll put money on it. ;-) In ...


0

You can filter the table using this formula in Column G =ISERROR(MATCH(B1;$E$1:$E$7;0)) supposed you have no column captions, otherwise adjust the range


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.


0

I think the following example matches what you requested. I created a VB subroutine to put a random value in B3, to simulate the changing value in your situation. The VB code also checks the current date and time against values in a list, and if they match, it records the random value in a cell next to the matching date and time. Not shown in the pictures ...


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.


0

You are on the right track. What you need first is to set up a new variable for the Excel.Application using late binding. It's under that object where all the normal excel VBA goodies hide. Below is an example vbs script that will open a new workbook, add a new sheet, name it "TEST", and add some content into cell A1 of that new sheet. When it's done, vbs ...


0

Something like this will create a list in sheet1 column A of Sheet Names, and every time cell C1 on any sheet changes, it will place the value of that cell into sheet1 in column B, next to the corresponding sheet name in column A It goes in the ThisWorkbook module through the VBA editor*. You can change the sheet name and ranges as needed. Private Sub ...


0

Option 1 to replace formating using a macro The following macro changes the heading from one style to another Sub FindAndReplaceStyle() Dim intI As Integer Dim newStyle As String For intI = 1 To ActiveDocument.Paragraphs.Count curStyle = ActiveDocument.Paragraphs(intI).Style If curStyle = "AxureHeading1" Then Call SetStyle(intI, ...


0

Without VBA something like this works - =IF(MOD(ROW(A1),3)<>1,"",INDEX(Sheet1!A:A,MOD(ROW(A1),3)+INT(ROW(A1)/3))) Replace the 3 with the nth row you want and drag it down & across on Sheet2, or whatever your sheet names are.


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

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 = ...


0

You'll want to re-roll for each monster (row) like this: Sub Fill() Dim Roll(1 To 8) As Variant Dim i As Integer i = Range("A3").End(xlDown).Row For j = 3 To i Roll(1) = Int((10 - 1 + 1) * Rnd + 1) + Range("E2").Value 'Initiative Roll(2) = Int((20 - 1 + 1) * Rnd + 1) - Range("F2").Value 'Attack Roll(3) = Int(((Range("G2").Value) - ...


0

I solved the issue by using a Collection and copy it afterwards to an array. Dim col As New Collection For Each a In range.Cells col.Add a.Value ' dynamically add value to the end Next Dim arr() As Variant arr = toArray(col) 'convert collection to an array Function toArray(col As Collection) Dim arr() As Variant ReDim arr(1 To col.Count) As ...


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


0

Paste the JSON into json-csv.com. Download the resultant CSV file. Open the CSV file up in Excel.


0

I could achieve what I wanted. I simple recorded first a Macro in order to see the details which objects have to be how accessed. Afterwards I finished the thing by making some optimization. Please refer below to the code extract, maybe helpful for others. Regarding the ordering I found in a forum the tip that you should assing another column "sort order" ...


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 ...


0

For older .XLS files: Open file in any hex editor Search for the text "DPB=" (no quotes, you should find it just above "[Host Extender Info]" Change it to "DPx=", save and close the file Open the file in Excel and press Yes if the following warning pops up: Open the VBA editor (Alt+F11) and press OK whenever the following warning pops up: ...


0

Some time ago I tried this for several other SU answers. The only way I've found is to use PowerShell instead of VBScript. PowerShell can execute every VBA command. Only the syntax can differ. Your VBA example as PowerShell script $excel = New-Object -ComObject excel.application $workbook = $excel.Workbooks.Open("C:\file.csv") $excel.visible = $true


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 ...


0

I would put the macros into an excel template. I would then create an autonew macro in the template to import the csv file and run the formatting macros. Then I would get the vbs to create and save the csv file and open an excel file based on the template.


0

There is a tricky way to do it. Create an object such as a button. Select this button, then hide it, and no cell will be selected. ActiveSheet.Shapes("Button 1").Visible = True ActiveSheet.Shapes("Button 1").Select ActiveSheet.Shapes("Button 1").Visible = False That's it.


0

This averages everything but the header with the header in Cell "B1" Dim GG as Long GG = Application.WorksheetFunction.Average(ActiveSheet.Range("B2", Range("B2").End(xlDown)))


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") ...


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 ...


0

Sub returnValues() 'last row used in the origin data sheet lastRow = Sheets("sheet1").UsedRange.Rows.Count 'counter for the rows to be pasted in sheet 2 n = 1 'loop through all rows For rowNum = 1 To lastRow 'test if all three cells in the row have data in them: If Not IsEmpty(Sheets("sheet1").Cells(rowNum, 1)) And _ Not ...


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.


0

The macro can only group the numbers in column 2-5, if the column name (Mr Bean), is situated beneath each other. How would the macro look like if it had to perform the same task, but Mr Bean is situated in random order: Mr Bean Mr X Mr Y Mr Bean


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


0

I had a similar problem with a Excel Macro tool written in the USA and I wanted to use it with in Germany. The tool seemed to get locked in a loop after starting so it didn't come to an end. I could finally solve this issue with changing the windows default for date, time, and number format, into US-standard like described here . So probably the VBA code ...


0

I would use the Power Query Add-In for this. It has Split and Combine commands that can convert delimited text e.g. 0,12,13,14 into a list and back. I've built a prototype which you can view or download - its "Power Query demo - compare single value in one cell with multiple values in other cell.xlsx" in my One Drive: ...


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 ...


1

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



Top 50 recent answers are included