Posts by acat

    Below code gives Not Blank if at least one cell in range is not blank, but I need code that will result in Blank if even one cell in range is Blank or has a number zero in it


    Code
    1. Sub IfBlank()
    2. If WorksheetFunction.CountA(Range("E6:E8")) = 0 Then
    3. MsgBox "Blank"
    4. Else
    5. MsgBox "Not Blank"
    6. End If
    7. End Sub

    How to Add x days to today in below code - this is run from Outlook Module
    And how to call a macro stored in ThisOutlookSession from macro stored in Outlook Module?


    Code
    1. xWdSelection.InsertBefore "Docs Attached: " & Format(Date, "mm / dd / yyyy") & vbCrLf & xFileName & vbCrLf & vbCrLf


    Because your code resulted in populating with "#NAME?", per my answer above in #3. I posted the code in #4 which does exactly what is needed. I further modified that and currently running code below:


    Hi Mario,
    You are probably close with the solution. I had to switch OrigName & NewName around, as I need OrigName range to populate with values from NewName. And it did populate, but with "#NAME?". The entire named range from A6:A1000 populated with it, where as NewName range has data from A6:A69 and it is similar to "12345 987654321" or "abcde 987654321"
    I tried code below, but last line comes up red and gives "Compile error: Syntax error"
    Replacing () with [] in those two lines gives "Run-time error'424': Object required" on line in red below


    Code
    1. Sub CopyNamedRange()
    2. Dim vr As Variant
    3. vr = ("Sheet1!NewName")
    4. [COLOR=#FF0000] ("Sheet4!OrigName") = vr[/COLOR]
    5. End Sub

    I have two named ranges of equal number of cells and same position on Sheet: Sheet4 OrigName and Sheet1 NewName.
    Want OrigName to populate with values from NewName.
    Getting "Compile error: Method or data member not found" on highlight below


    Code
    1. ThisWorkbook.Range("Sheet4!OrigName").Value = ThisWorkbook[B][COLOR=#FF0000].Range[/COLOR][/B]("Sheet1!NewName").Value

    On my Excel spreadsheet I have a list of file names in A6 and down. Path to folders where those files are is in A3. Trying to add an easy option for user to be able to change the actual file name by changing the name of that file on that Excel spreadsheet.
    So far I have A1 keep the old file name every time selection changes (code: Range("A1").Value = Range("A" & (ActiveCell.Row)).Value) and then Before Right Click triggers below code to rename old to new file name.


    Problem is that I noticed some of the actions I do to the files with this tool (e.g. moving them to different folder) have started causing Excel to freeze before proceeding, whereas before I added this renaming solution it was running smooth, like butter.


    So looking for a better solution...



    On my Excel spreadsheet:
    A3 has a path to folder and \.
    A6 and down have file names listed from A3 path.
    Need VBA to detect once cell value in Range("A" & (ActiveCell.Row)).Value has changed and to rename the corresponding file from old cell value name to the new cell value name.
    So far I thought of whenever there is a selection change in A Column Excel keeps old A value into cell O1 by: Range("O1").Value = Range("A" & (ActiveCell.Row)).Value
    Now it seems just need proper code to rename from old to new... So I want to be able to change cell value on Excel spreadsheet and as soon as I Tab out of the cell the old file name changes to new.

    To reiterate the Goal: Looking for modification of this code for selection of the next file to take place without opening new or closing existent File Explorer window, i.e. select new file in the same File Explorer window. Closing it and then opening a new one before selecting next file from the list was never an objective to start with, just ended up being something that kinda works. Eliminating the closing part should also address the unintentional IE closing.


    Goal: select new file in the same File Explorer window.


    Hope this clarifies