Tag Info

New answers tagged

1

Each TextRange has a Runs collection. If all of the text in the range is identically formatted, there'll be only one Run in the collection. If the font/size/color/boldness changes midstream, the Runs collection will be incremented. Accessing each Run in the collection gives you a TextRange whose font properties you can examine. Assume for example that ...


0

If VBA (the command button!) is not a mandatory option you can achieve the same as shown below. Formula =IF(MIN($A$1:$A$3)=A3,SUM(B3:D3),"") I used your example with A1:A3 and B1:D3 to illustrate this answer. Please comment if command button is mandatory - then I will add an edit to add the VBA code to my answer


4

To keep it simple, just add following rows to your code before "strFilename = strOutputFolder & "\" & strItem": strItem= replace(strItem, "~", "_") strItem= replace(strItem, """, "_") strItem= replace(strItem, "%", "_") strItem= replace(strItem, "#", "_") ' and so long ... strFilename = strOutputFolder & "\" & strItem [UPDATED] Well, as ...


1

You use your variable mystr in this line - With ActiveSheet.QueryTables.Add(Connection:=mystr, Destination:=Range("$A$1")) mystr = "URL;http://apps.webofknowledge.com.ezproxy1.lib.asu.edu/Search.do?product=WOS&SID=1BzJbqm9xDW5Yzj4rhB&search_mode=GeneralSearch&prID=44dc4b3a-138a-4532-8429-772860bea673" But then you redefine it before calling ...


0

You may need to use the following event: Private Sub Worksheet_Change(ByVal Target As Range) Instead of: Worksheet_Calculate() If you have the code in Worksheet_Calculate, it might not execute if the worksheet doesn't need to be recalculated (i.e. there are no cells with formulas in the worksheet) Paste this into the code window of the sheets that you ...


0

On both cases you use range.text which is a string variable, it gives you only the text in the first cell in your range, you need a bit more complex code. If i understand well you just want to test whether you cross are not blank and not interested in the content. Here are some tips to set your criteria: use worksheetfunction to get number of non-empty ...


1

We will just use formatted values: Sub qwerty() Dim i As Integer Dim s As String i = 2 Do Until Cells(i, 1).Value = "" If (s = "") Then s = Format(Cells(i, 1).Value, "0000") Else s = s & ", " & Format(Cells(i, 1).Value, "0000") End If i = i + 1 Loop Cells(1, 2).Value = s End Sub


2

You need to use the Value property of the Range object instead of the Formula property: ws.Range("A" & lastRowofA & ":A" & lastRow).Value = "datewanted" If datewanted is a variable in your VBA code containing a value to be inserted into your cells, simply drop the surrounding quotes: ws.Range("A" & lastRowofA & ":A" & ...


0

You need to declare them outside of any function or routine, but you cannot do anything with them unless within a function or routine - Option Explicit Public Const con1 = 4 Public Const con2 = 5 Sub posone() MsgBox (con2 - con1) End Sub Sub negone() MsgBox (con1 - con2) End Sub Each variable needs to be declared public. You've done that, but you're ...


1

So, the issue is that Workbooks(wbs) is not recognized as a Workbook object. This is because the argument in parentheses is expected to be an index, i.e., an integer, not a file path. One solution is to loop through the Workbooks collection and compare your desired path to the paths of open workbooks. Then set a Workbook object equal to the matching ...


0

Try this: Private Sub Worksheet_Change(ByVal Target As Range) Dim wkb As Workbook Dim wks As Worksheet Dim wks1 As Worksheet Dim cell1, cell2, myrange As Range Set wkb = ThisWorkbook Set wks = wkb.Sheets(1) Set wks1 = wkb.Sheets(2) targetrow = Target.Row targetcolumn = Target.Column task = wks.Cells(targetrow, 3) For i = 3 To 300 a = wks1.Cells(6, i) ...


0

If instead of trying to do everything in one go, you were to mark anything you want deleted with a Category. You could then create a virtual search folder for that category. This has the advantage of working across all actual folders. You could then write some simple VBA that is executed at startup and shutdown of Outlook that deletes the contents of that ...


0

The formula solution would be difficult to implement / debug, prone to errors, and greatly increase file size. The VBA procedure could be broken into steps: Delete all the sheets created last time the macro ran (this is easiest if the log you're showing is the only sheet in the workbook but you can adjust to anything) Get a unique list of the Acct names ...


0

Without knowing the rest of your code or what you're trying to do, this should answer your specific question - Sub test() If Hour(Now) >= 14 And Minute(Now) >= 30 Then 'do stuff Application.Dialogs(xlDialogSendMail).Show End If End Sub


0

Literally, what you want translates to something like this in the worksheet module for sheet 1 - Sub worksheet_change(ByVal target As Range) Dim actdatex As Integer Dim actdatey As Integer Dim newcell As Range Dim rngdate As Range If Not Intersect(target, Range("A2:A999")) Is Nothing Then On Error GoTo handler For Each c In Range("Sheet3!C6:AK7") ...


0

Give this a try: Public Function GetLastValue(rIn As Range) As Variant Dim N as Long N = Cells(Rows.Count, rIn.Column).End(xlUp).Row GetLastValue = Cells(N, rIn.Column) End Function and use it like this: NOTE: this is in the desired format: =GetLastValue(A:A)


0

AS eirikdaude wrote, there are many ways to accomplish your primary task in VBA. To accomplish the conversion of a string into a range object referring to the entire column, you can use something like: Function LastValueInColumn(COL) Dim R as Range Set R = Cells(1,COL).EntireColumn This will work (set R to the desired column) provided COL is a valid ...


1

Either just use the worksheet-formula in your macro as well, i.e. Function GetLastValueInColumn(r As Range) As Range GetLastValueInColumn = Application.WorksheetFunction.LOOKUP(2,1/(r<>""),r) End Function Or use some VBA specific methods, a couple are detailed in this answer on StackOverflow.


-1

Private Sub PG1() Range("$E$51").EntireRow.Hidden = (Range("$E$50").Value = "Passed") End Sub


0

Does this improve the situation?? Function InRange(Range1 As Range, Range2 As Range) As Boolean 'returns True if Range1 is within Range2 Dim InterSectRange As Range InRange = False If Range1 Is Nothing Then MsgBox "Range1 is nothing" Exit Function End If If Range2 Is Nothing Then MsgBox "Range2 is nothing" ...


0

You can use OnKey to assign a macro to just about any single keystroke. See: https://www.youtube.com/watch?v=MbHYJOvdv8s


1

Step-1: I donot know if you have the developer tab or not in your Excel. If you do not see a "Developer" tab next to your View tab, follow the instructions here. to get it done. Step-2: As shown below locate the command button and click on it. This allows you to draw the command button in the location you like. Step-3: Click on the button next to ...


0

Create a ninth wrapper macro that calls each of the 8 macros in turn, and assign that wrapper macro to the control button.


0

In all versions of all Office applications since 2003, and possibly earlier, Alt+F11 will open the VBA Editor. There is no need to enable the 'Developer' tab in the Ribbon for Office 2007 and newer for this to work.


0

Do you want the text to appear in the email as multiple paragraphs or simply appear in the code as separate lines? For the former: eply.Body = "Dear Client," & vbNewLine & vbNewLine & " We thank you for consulting our firm regarding your legal issue." & vbNewLine & vbNewLine & " Unfortunately, ..." In other words, you need 2 ...


0

Try using vbNewLine and _ like so: Reply.Body = "Dear Person" & vbNewLine & vbNewLine & _ "Line1" & vbNewLine & _ "Line2" & vbNewLine & _ "Line3" & vbNewLine & vbNewLine & _ "Sincerely," & vbNewLine & _ "Me" vbNewLine represents a newline character _ tells VBA that the current statement isn't finished.


0

I haven't used VBA in Ms Outlook or much of VBA at all but if there is a line length limit, you could define separate variables and concatenate them e.g. a="asdf" b="qwert" c= a & vbNewLine & b So, you know how to concatenate strings, as you already have, but you could just also, use separate variables


2

The full path to the root User folder of the currently logged-in user can be retrieved with: Environ("USERPROFILE") To save the file to the user's desktop, like you've done, you would use: Environ("USERPROFILE") & "\Desktop\" & ... For your specific code as provided ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ ...


1

Create a boolean variable, set it to false before the loop and change it to true only when the comparison of dates is true. Then, before sending the email check the state of the variable. You can do these changes: 1 - Before the loop, above the line For Each rngCell In .Range(rngStart, rngEnd) put the line ValidDate = False. 2 - After If rngCell.Value - ...


0

Here is another User Defined Function that will return all of the digits after a particular string (prefix). In the arguments, you specify the string to be processed, and the prefix to be used. You can also specify if the prefix should be case-sensitive. The default is for it to NOT be case sensitive, but that is easily changed. The UDF uses regular ...


0

I'd use a Excel UDF (User Defined Function) for this. Set it up like this: Public Function mylookup(inputrange As Range, match As Range) As String Dim arr() As Variant Dim d As Object Dim result As String Dim i As Integer Dim v As Variant Set d = CreateObject("Scripting.Dictionary") arr() = inputrange.Value For i = 1 To UBound(arr) If arr(i, 1) = ...


1

I may not understand your example clearly, but it looks like you could just use one of these cell formulas: =IF(ISBLANK(B5),"",TODAY()) =IF(ISBLANK(B5),"",NOW()) =IF(ISBLANK(B5),"",Updating_Date(B5)) If you want to do the check in VBA, here's an option: Function Updating_Date(dependent_cell As Range) If LenB(dependent_cell.Value) > 0 Then ...


0

DoCmd.SetProperty testfield, acPropertyValue, bucket Using the above code, you are attempting to set the property for the control, not change the value. A property would be background color, height, width, etc. If want to set the value, you need to simply use the me function to refer to control on a form. me.testfield = bucket This tells access to ...


2

if you are using Excel 2010 and above, then Excel provides you with in internal add-in which allows you to save the file as PDF format. Use the following code in your macro to save your file as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\46506090\Desktop\Book1.pdf", Quality:=xlQualityStandard, _ ...


0

To find the value of exactly two digits after "J*", you don't need VBA. If the string is in A1, just use: =VALUE(MID(A1;SEARCH("J*";A1)+2;2)). The SEARCH function searches for "J*". MID takes the next two characters and VALUE converts those to a value.


0

It’s too bad that you didn’t understand the subroutine that you were cannibalizing better before you started modifying it. The “WalkThePlank” subroutine is looking for matches.  When it finds a row in Sheet1 that matches a row in Sheet2, it takes action.  You’re looking for unique values; i.e., rows in Sheet1 that don’t match anything in Sheet2, and vice ...


0

Just as you had to insert a space at the end of "wscript C:\test.vbs " you will also need a space between two variables Shell "wscript C:\test.vbs " & strName & " " & fileDir You will however get problems if either strName or fileDir already contains spaces. If you can not rule that out (e.g. because you know exactly what the names and ...


0

This addresses your question rather than your sample code. If you have a string like: q1w2e3r4asJ*66bvft654 and want to extract the two digits following J* then try this little UDF Public Function RetDigits(sIN As String) As String Dim lookFor As String, v As String lookFor = "J*" RetDigits = "" If InStr(sIN, lookFor) = 0 Then Exit ...



Top 50 recent answers are included