Hot answers tagged

9

You're missing the For in For Each: Sub Removetables () Dim oTable As Table For Each oTable In ActiveDocument.Tables oTable.Delete Next oTable End Sub


2

One way to speed up execution is to avoid recomputing the same values repeatedly in a loop. Example: Worksheets("Program").ListObjects("Program").Range Assign it to a variable (example:programRange) and use the variable to do the subrange selection. You can do the same with the oLastRow expression. Second, as some computations depend on the date ...


2

The Sheets global collection is really referring to the the active workbook. Avoid implicit references! Worksheets have a (name) property. If you didn't change it, then the object referred to by Sheets("Sheet1") can be accessed directly with the Sheet1 identifier - assuming that's the case, this would work: Sheet1.doThisFunction Also note, the ...


2

There are several ways to retrieve the current user name. If you want to retrieve the OS user then this is the best solution: Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, lpnSize As Long) As Long Public Function GetOSUser() As String Dim lpBuffer As String * 25 GetUserName lpBuffer, 25 ...


2

To automatically bring the current month's tab to the foreground after opening a workbook, you can simply use this: Private Sub Workbook_Open() Sheets(Format(Now(), "mmmm")).Select End Sub NOTE: This will only work if the month names are in the same language than the OS.


2

Ignore this answer if you receive a "formula-only" answer that meets your requirements. This small UDF() will return the first number in a string meeting your requirements: The code: Public Function GetNumber(sIN As String) As Long Dim L As Long, i As Long Dim s As String s = sIN L = Len(s) For i = 1 To L ch = Mid(s, i, ...


2

Where you are setting the variable copyTo is outside of the loop so it always just uses the folder value in in D1. bring colD=... and copyTo... inside your loop (somewhere after x=x+ and it should work better. Option Explicit Sub copyTemplate() Dim lRow, x As Integer Dim wbName As String Dim fso As Variant Dim dic As Variant ...


2

Where are you trying to find "EE Status"? Just restrict your search to that, and .Find returns a range So, for example, something like (untested) Dim findIt as Range Set findIt = Sheets("Raw Data").Cells.Find (...) Dim lastRow as Long lastRow = Cells(Rows.Count,findIt.Column).end(xlup).row dim myRange as Range Set myRange = Range(findIt, Cells(lastRow, ...


1

I did manage to solve it by first defining names for the ranges in the external workbooks in the 'master' workbook. By doing that I'm keeping the formula below the 255 character limit of .FormulaArray. A good nights sleep and fresh eyes on the problem was all it took :-)


1

Use PR_SEARCH_KEY (http://schemas.microsoft.com/mapi/proptag/0x300B0102 for PropertyAccessor and Folder.GetTable filter).


1

I'm getting a error: Invalid procedure or argument. Set Fileout = fso.OpenTextFile(filePath, ForAppending, TristateFalse) You are passing TristateFalse as argument 3. It should be argument 4. Please read the documentation. Description Opens a specified file and returns a TextStream object that can be used to read from or append to the file. ...


1

If you're sure that extra character is always +, then use =SUBSTITUTE(A1,"+","")


1

Try this small UDF(): Public Function comparee(s1 As String, s2 As String) As String comparee = "No Match" If s1 = s2 Then comparee = "Exact match" Exit Function End If arr1 = Split(s1, ", ") arr2 = Split(s2, ", ") For Each a1 In arr1 For Each a2 In arr2 If a1 = a2 Then comparee = ...


1

Select the cells you wish to examine and run: Sub fourmat() Dim r As Range, msg As String msg = "" For Each r In Selection msg = msg & vbCrLf & r.Address(0, 0) & vbTab & r.FormatConditions.Count Next r MsgBox msg End Sub It will list each cell and the number of conditional formats associated with that cell: ...


1

In your Word doc, you could create one bookmark and then cross-reference the bookmark text. So you would create/set the bookmark for a particular value in one place and then (instead of creating multiple other bookmarks for the same value) at each other place you want the same value to appear, insert cross-references. In the Cross-reference dialog, just ...


1

To get the name of the current month: Sub tretdc() tabstr = Format(Now(), "mmmm") MsgBox tabstr End Sub


1

Here's a formula I think works. =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), ((1/MAX(IFERROR(1/( ISNUMBER((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1))))*1)* (LEN((TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (ROW($1:$25)-1)*LEN(A1)+1, LEN(A1)))))>=7)* (LEN((TRIM(MID(SUBSTITUTE(A1," ...



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