Tag Info

New answers tagged

1

Okay, have fixed issue, and tested to confirm working Issue was I attempted to reference the row ID based on a non-existant variable, not the variable which was being looped. For posterity sake: Make sure every time trying to use a variable, you are using the right variable already declared. Fixed code for reference: Private Sub MultiBox_Change() Dim ...


1

I see, this could be a best option for you to set a meeting reminder of your outlook. A VBA script is provided in the following URL, may not be as like red alert but a worthy one. Please try and check you results. Outlook appointment reminders Create a Digital certificate for later Hit Start and type ‘certificate’, select ‘Digital Certificate for VBA ...


1

This Macro is sheet specific and must be put in the sheet vba module. Right click on the sheet name at the bottom of excel and select view code. Paste this code there. close the vba module and change A10 on the first sheet. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False For Count = 1 To ActiveWorkbook.Sheets.Count ...


0

I assume A10 is a variable, otherwise you could just hard code it, and each successive sheet is incremented by 1, regardless of the value in sheet 1. In A10 on sheet 2, you could put this: =IF(ISBLANK(SHEET1!A10),"",SHEET1!A10+1) On each successive sheet, change the sheet reference to reflect the previous sheet number. The ISBLANK function tests to see ...


0

You don't need VBA to do what you want. You want to subtract 60 repeatedly until the remainder is less than 60. You could write VBA code to do that, but you can do that in one step with a formula: =MOD(ED1,60) If you divide 1000 by 60, you get 16.666... That means that you would need to subtract 60 sixteen times to get something less than 60. The ...


0

I have great news! Because the value in ED1 is a constant, the formula will always result in the value: 960 Let's assume the long range is A1:Z100 Here is the VBA based solution: Private Sub Workbook_Open() Sheets("Sheet1").Range("A1:Z100").Value = 960 End Sub This is a workbook event macro. Because it is workbook code, it is very easy to install ...


2

Adding this line and running the macro with the document open in Print View inserts the settings.xml Element: activedocument.ActiveWindow.View.DisplayBackgrounds = True (Not easily deduced from the recorded code!)


0

You've mentioned that you would like this to automatically copy the information to the Maint Due, so I will work with that. A Worksheet change event macro could cover the automation but you would need one for each of the Monthly and Annual worksheets. I'll suggest a Workbook_SheetChange that parses the source worksheet looking for either Monthly or Annual. ...


1

I figured it out, it's super simple. All you need to do is to change the number value of whatever you're using as your "Cell link," which you can easily do in VBA.


0

I have similar problem. Excel on this page helps me. http://www.excelclout.com/microsoft-update-breaks-excel-activex-controls-fix/ Just open it, click on button and reopen your old excel. Cheers.


2

Check out the Worksheet event Worksheet_Calculate or the Workbook event Workbook_SheetCalculate It's easy for those to start firing infinite loops, though, so be careful


1

No addon making required! Store it in a special workbook called Personal.xlsb Steps: This advanced tip shows you how to make macros available across all your workbooks, by saving them to a special workbook that Excel loads each time it is opened. http://youtu.be/xWL78KcDVGU If you found this video helpful, kindly edit this answer to add transcribed ...


0

This does what you want. In your example, you have 4 columns (C, D, E and F). Well, you could add another (G, H, I, J etc) and the code below will still execute for all of these columns. You will need to tell it a few things though, such as rowsRequired (in this case this is row 2, it is the row where you define the requirements (such as how many values ...


0

The offending update is KB2553154. The KB article lists known issues with this Security Update including the Cannot Insert Object error message when attempting to interact with a Forms ActiveX control in an Office document. A resolution for the issue is described in KB3025036. The resolution is to delete cached versions of the control type libraries, which ...


1

Okay, fixed it all up. :) And answering my own question lol - Just for googles sake. Private Sub Workbook_Open() Const fromFile = "NetworkDriver\Folder\Workbook.xlsx" Application.DisplayAlerts = False Sheets("Sheet8").Delete Sheets("Sheet9").Delete Application.DisplayAlerts = True Dim srcBook As Workbook Dim wkb As Workbook Set wkb = ActiveWorkbook ' ...


0

You'll want to use Conditional Formatting. Put the cursor in the first cell of the column (I'm going to assume its A1 for this example). Create a Conditional Formatting rule with "Use a formula to determine which cells to format" with the following formula: =IF(ISBLANK(OFFSET(A1,1,0)),TRUE,FALSE) Once the rule is created, right click, select Format ...


1

Here is some code to get you started (based on the cell placement in your image). Sub SeriesFill() Dim RowCount As Long Range("C4:C1000").Cells.Clear RowCount = Range("C2").Cells.Value + 4 Range("C4").Cells.Value = "0" Range("C4").AutoFill Destination:=Range("C4:C" & RowCount), Type:=xlFillSeries End Sub This was tested in Excel ...


0

I have pasted my answer which highlights multiple searched word (from other file) in yellow in colour.Search word file and file to be highlighted can be chosen using file dialog object. Dim filepath As String Dim filename As FileDialog Dim stylepath As String Dim stylename As FileDialog MsgBox ("Please choose Style File Name") Set stylename = ...


0

The latest Windows update killed it. Back out the ones involving Excel and VBA and it will run again.


0

If you use an Excel Table you don't need to autofill anything. The total row will move automatically. Data validation, conditional formats and formulas will automatically be applied to new rows.


-1

I am not sure if this helps but... Make sure that your Developer tab is set to be visible. Go to File>Options>Customize Ribbon>Main Tabs (on left) Make sure that there is a check in the box for the Developer tab OK (save)


3

Press F4 to display project properties. Name the VBAProject for your personal workbook Personal Create a subroutine that calls beep. Public Sub MakeItBeep Beep End Sub In the workbook you want to enable this in, click on Tools>>References on the menu bar. Click Browse... Find your Personal.xls file and select it. (It could be in a number of different ...


0

You can do this with the following VBA code. It counts the shapes in the document, checks their width against the available space on the page, and resizes if necessary. Note that Word has two different collections for Shapes and InlineShapes, hence the two different For loops. Also, it uses a series of If/ElseIf statements to identify the page width based on ...


0

This example will copy the cells A1:A2 from Excel to Word Using bookmarks Sub PopulateColumninWord() Dim wdApp As Object Set wdApp = GetObject(, "Word.Application") Set wdDoc = wdApp.ActiveDocument 'Read in position of two bookmarks named START and END Set rngStart = wdDoc.Bookmarks("Start").Range Set rngEnd = ...


3

If you only want to keep track of the changes from down to up (assuming your data is in column A) then all you need is =N(A2>A1) then drag the formula down. This evaluate to TRUE if the value has increased (gone from down to up), then N(boolean) converts to this to 1 or 0. To keep track of all state changes you'd use =N(A2<>A1) To count all state ...


1

Here is the equivalent VBA code to count the number of cells containing at least one string Sub StringCounter() Dim r As Range, s As String, i As Long s = "*string*" For Each sh In Sheets Set r = sh.Range("C2:C150") i = i + Application.WorksheetFunction.CountIf(r, s) Next sh MsgBox i End Sub EDIT#1: Here is the code to ...


0

You can write a pretty straightforward user-defined function in VBA to do this. Paste the following code into a new module (for instructions on how to do this, see this post): Public Function WBCountString(SearchFor As String, InRange As Range) As Long Dim wbcs As Long, rng As Range, addr As String For Each s In Worksheets 'Limit search area to ...


-2

Sub CopyPasteSpecVal_1() Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub


5

My solution is assuming clean data starting in cell A2 and going down column A. For my formula it also assumes no data past row 1000 (adjust this upward for most real world data. Formula is an array formula so enter using ctrl+shift+enter =SUM(IF(A2:A1000=0,IF(A3:A1001=1,1,0),0)) How the formula works. For each cell A2 to A1000 look for a zero, if ...


7

You can get the count with a single SUMPRODUCT formula. If your data is in A1:A25, you could use the following: =SUMPRODUCT((A1:A25=0)*(A2:A26=1)*(NOT(ISBLANK(A1:A25)))) This counts only instances where one value in the column is 0 (and not blank) and the following value is 1.


1

You need to define each range for each sheet e.g. COUNTIF(Sheet1!C2:C150,"*string*")+COUNTIF(Sheet2!C2:C150,"*string*") ... If you want to be clever, you can say put each sheet in the column A, and in column B use =COUNTIF(INDIRECT("Sheet"&A1&"!A1:A5"),"*string*") Or whatever fits your naming scheme. Then drag it down for each count and sum ...


2

You can use formula to detect transitions from 1 to 0 and from 0 to 1. Just be careful around blank cells! Consider following example (see screenshot below): column B contains binary data pattern. C2 will contain following formula: =IF(AND(NOT(ISBLANK(B3)),B2=1,B3=0),1,0) D2 will contain: =IF(AND(NOT(ISBLANK(B3)),B2=0,B3=1),1,0) You will just need ...


1

Enter a 3-dimensional formula. This will evaluate every worksheet between (and including) the start (Sheet1) and end (Sheet3) worksheets you're interested in. =COUNTA(Sheet1:Sheet3!A1:A99)


0

Determine the length of the template text and keep only that. Let's say the start of the signature is unique. Dim sig_start as long Dim template_text_end as long sig_start = InStr(msg.Body, "unique text at the start of the signature") template_text_end = sig_start - 1 msg.Body = Left(msg.Body, template_text_end) debug.print template_text_end When you ...


0

Typically, the .responseText is parsed as an HTML document but it can be handled with string functions as well. You seemed comfortable with Mid, Instr, etc so I stayed on that path. This does not start a new worksheet; merely writes into the current one so start on a new blank worksheet before you run the macro. Sub Retrieveprice() ' in the references ...


1

There is no such event. The only thing you can do is to check the state of your rules periodically. Another way is to write the COM add-in that will advise to IMsgStore's fnevObjectModified.


0

From the MSDN entry regarding QueryTable.CommandType Property (Excel): You can set the CommandType property only if the value of the QueryType property for the query table or PivotTable cache is xlOLEDBQuery. You're not making an OLEDB query so the QueryType is something else. Since it's something else, then you can't set the CommandType, so just ...


0

You can have two data sheet among which one is master and other is the one you can share with the specific group. You can write a VBA script for the master data sheet to make a copy of the required information to a new data sheet which can be shared with editing privilege to the specific group. The data sheet that is shared with the specific group will have ...


1

My VBA is a bit rusty, but if you insert with the same name, and give it an index property, you can use item(0) to select the first, item(1) to select the second, etc... where item() is the name of your instance, and 0,1,2,3... is which item it is. It may be as simple as adding: dim MyShapes MyShapes(0) = Application.Templates( ... ) Edit by john.s2011: ...


0

You put a for loop around it, but never use "i" for anything. That's probably why it's not working. ;) Try something like this (warning this is untested, and has no conversion error checking implemented): Dim startYear As Integer Dim endYear As Integer Dim strStartYear as String For startYear = 1942 To 2014 ' Convert the current start year number to ...


2

Cleraing all sheets in a workbook: Sub ClearAll() Set wbook = ActiveWorkbook For Each sht In wbook.Worksheets sht.Activate sht.Cells.Select Selection.ClearContents Selection.ClearFormats ' edit: clear formats too sht.Cells(1, 1).Select ' edit: select the first cell to cancel selection of the whole sheet Next ...


0

Simple as below. Change the Sheet1, Sheet2 to the names of your Sheets and add as many of those 3 lines as you need. I assume you know how to link create a button in excel and link to a Macro. Sub ClearSheets() Sheets("Sheet1").Select Cells.Select Selection.ClearContents Sheets("Sheet2").Select Cells.Select Selection.ClearContents ...


0

If just changing the quotes from "" to """" doesn't fix it then maybe you should try setting the value property of the range object, instead of the range object itself. Sheets("Invoice").Range("G11") = "=IF(ISNA(VLOOKUP(B11,INVENTORY!A$1:D$2501,INVENTORY!C$1,FALSE)),"",(VLOOKUP(B11,INVENTORY!A$1:D$2501,INVENTORY!C$1,FALSE)))" Your are trying to set the ...


0

In VBA a " is used to enclose a string. To use a " inside a string, use "". What goes wrong in your formula is that VBA converts the middle two quotes to one quote. If you replace the "" in the middle with """", you should be good to go. See this Stack Overflow link for reference.


0

If you are opening the documents, say by double-clicking in Explorer, you are using the same instance of Word. I believe you are being made to believe there are separate instances of Word. Click the File Tab then click Options. Click Advanced. Scroll down to the Display section Have you ticked ‘Show all windows in the taskbar’? Regardless try this ...


0

Suggestion, assuming you have Word: Create a LIST in Excel that has all the data about ONE employee on ONE ROW. Then use that Excel sheet as the ODBC source for a plain Mailmerge in Word.



Top 50 recent answers are included