isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    Code:
    '=====================================
    ' Start Copy/Paste Values only section                  Column D -> H, Rows 3 -> 52
    '=====================================
    ws.Range("D3:H52").Copy                                 ' Copy the data from more stuff (Row 3) -> stuff # (Row 52)
    ws.Range("D3:H52").PasteSpecial Paste:=xlPasteValues    ' Pastes value only, dropping the array created by xxxx
    Application.CutCopyMode = False                         ' Clears values so not to double paste if users clicks on cell or hits Return
    
    
    '=====================================
    ' If stuff = null fill range with FALSE
    ' Leave IS/M (Row 27) blank
    '=====================================
    
    
    ' Cells(Row, Column) -> Row 3, Column 8 -> H3
    If IsEmpty(Cells(3, 8)) = True Then
        ws.Range("H12:H26").Value = "FALSE"
        ws.Range("H28").Value = "FALSE"
    End If
    the range H12:H28 have a set of arrays that pull data from another workbook. The copy/paste special values works, but until I run the code a 2nd time, the IsEmpty is still seeing the array data and not the empty value.

    Is there a better option and one that will work without having to issue the code a 2nd time?

    By null I just mean no value entered into cell. Before the copy/paste section runs there is an array in all cells in row 3. After the copy/paste there should only be either a name or a blank cell. The IsEmpty does not see the blank cell the first time the code runs. However the 2nd time I run the code IsEmpty will then see the cell in row 3 as null and populate the correct "FALSE" output in the ranges indicated.

    Thank you,

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    Try
    Code:
    Ws.Range("D3:H52").Value = Ws.Range("D3:H52").Value
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Oct 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    I am guessing that is instead of the .PasteSpecial Paste:=xlPasteValues
    Last edited by LlebKcir; Aug 21st, 2019 at 01:41 PM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    Sort of, it replaces these three lines of code
    Code:
    Ws.Range("D3:H52").Copy
    Ws.Range("D3:H52").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Oct 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    Quote Originally Posted by Fluff View Post
    Sort of, it replaces these three lines of code
    Code:
    Ws.Range("D3:H52").Copy
    Ws.Range("D3:H52").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    That worked, but why did it work? Time for me to learn.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    If you have a formula that returns "" when using copy/paste it will put a nullstring into that cell so that it isn't empty, but using .Value=.Value doesn't do that.
    Why, I have no idea, It's just the way it is.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Oct 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    That is good to know. I will keep that in mind. Also would have sped up that section of code as I spent far to many hours hunting for examples and instructions on that section :D.

    Well here is the full functioning code as things sit.

    1. Copy/Paste Value using this adjustment of .Value = .Value to remove all array formulas
    2. On Title cells that are blank, populates correct cells in the column with value "FALSE"
    3. Saves a local copy with a set uniform file name

    Side note question, instead of performing the save as, would it be possible to keep the original file open and save a new workbook populated with the .Value = .Value and filled in FALSE values without making edits to the original? That might speeds up some of the next processes I am attempting to automate.

    Thank you again Fluffy

    well duh, i should put the code up:

    Code:
    Sub FOOsaveValues()
    
    '=====================================
    ' Set Variables
    '=====================================
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim Network As String
    Dim Group As String
    Dim sFName As String
    Dim Def As String
    
    
    Set wb = ActiveWorkbook                 ' Used as shortcut for Workbook
    Set ws = wb.Worksheets("FOO Table")     ' Worksheets shortcut to read data from FOO Table
        ' Read Cells(row, column).Value and store as variable - C1, Network name
    Network = ws.Cells(1, 3).Value
        ' Read Cells(row, column).Value and store as variable - G1, Group #
    Group = ws.Cells(1, 7).Value
        ' Final output will be in format: FOO_Table-Network_Group_1.xlsx
    Def = "FOO_Table-" & Network & "_Group_" & Group
    
    
    '=====================================
    ' Start Copy/Paste Values only section                  Column D -> H, Rows 3 -> 52
    '=====================================
    ws.Range("D3:H52").Value = ws.Range("D3:H52").Value     ' Sets the value from the array output, clearing the array formula
    
    
    '=====================================
    ' Fill null JUNK with FALSE
    ' Leave IS/M (Row 27) blank
    '=====================================
    
    
    ' Cells(Row, Column) -> Row 3, Column 5 -> E3
    If IsEmpty(Cells(3, 5)) = True Then
        ws.Range("E12:E26").Value = "FALSE"
        ws.Range("E28").Value = "FALSE"
    End If
    
    
    ' Cells(Row, Column) -> Row 3, Column 6 -> F3
    If IsEmpty(Cells(3, 6)) = True Then
        ws.Range("F12:F26").Value = "FALSE"
        ws.Range("F28").Value = "FALSE"
    End If
    
    
    ' Cells(Row, Column) -> Row 3, Column 7 -> G3
    If IsEmpty(Cells(3, 7)) = True Then
        ws.Range("G12:G26").Value = "FALSE"
        ws.Range("G28").Value = "FALSE"
    End If
    
    
    ' Cells(Row, Column) -> Row 3, Column 8 -> H3
    If IsEmpty(Cells(3, 8)) = True Then
        ws.Range("H12:H26").Value = "FALSE"
        ws.Range("H28").Value = "FALSE"
    End If
    
    
    '=====================================
    'Start Save As process
    '=====================================
    sFName = Application.GetSaveAsFilename(InitialFileName:=Def, FileFilter:="Excel Files (*.xlsx), *.xlsx, Macro Enabled Workbook" & "(*.xlsm), *xlsm")
    
    
        If sFName <> "False" Then                   ' Checks that sFName has not been cancled
          If Right(sFName, 4) = "xlsx" Then         ' If saved as *.xlsx this section provides correct file type 51
             Application.DisplayAlerts = False      ' *.xlsx is no macros enabled
             ws.SaveAs sFName, 51
             Application.DisplayAlerts = True       ' Displays all errors from SaveAs
          ElseIf Right(sFName, 4) = "xlsm" Then     ' If saved as *.xlsm this section provides correct file type 52
             ws.SaveAs sFName, 52                   ' *.xlsm is macro enabled
          End If
        End If
    
    
    End Sub
    Last edited by LlebKcir; Aug 21st, 2019 at 01:56 PM.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    You're welcome & thanks for the feedback.
    You could create a copy of the workbook & than edit that, but I don't know if it would be any quicker.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Oct 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    Further details might help you help me.

    I have three workbooks, we can call them: Master, Table, Config
    I have semi-automated via formulas the config and Table workbooks. Table pulls data out of Master via arrays, but is locked to having the Master open or the data clears from the workbook. Thus what you see above. That takes the Table workbook, makes a copy of just the output from the arrays, fills in gaps required for the Config workbook, and makes a local copy with a uniformed name.

    The Table workbook, just a single worksheet, is copy/paste into the Config workbook. This then populates data across all of the worksheets except 3, those still require manual entry and for the foreseeable future will always require manual updates. Just the nature of the workbook.

    I would love to be able to create 2 options of the Table out of the Master. 1 that is much like the process I have now, but another that can grab an entire network and make as many workbooks as required to complete the full group requirement. This is part of the last question I asked. If that process could be automated to build Table workbooks for an either network based on a few entries that could save many hours of open, run, save, re-open, wash/repeat.

    I would like like to be able to import the data instead of copy/paste from Table into Config as that, while not much, will save time. Before I started with any kind of Excel automation these processes could take more than 2 days. I have reduced the time to under 1hr so this is good progress. Still can take longer on complicated conversions, but those will be completed before end of next year so meh...

    The Master has multiple worksheets each can contain more than 1 network
    The Table is just 1 group within a specific network containing all data needed for Config
    The Config has data for other automation processes. Currently with a copy/paste 98% of the Config auto-completes. The rest is mark up notes that will remain manual. If I could eliminate the manual copy/paste (this has at times caused issues with the formulas not updating until all of the TRUE/FALSE values have been manually retyped, very annoying and causes to much time) and for something along the lines of Table.Range().Value = Config.Worksheet.Range().Value that would be fantastic.

    Successes:

    From the Master I can open the Table stored on a ShareNow web page.
    Above code in this thread is 100% successful at a 1 off new Table workbook without editing or Check-Out/In process on the ShareNow file

    Fails:
    Attempted to combine the Save As portion with the code on Master. That would ONLY attempt to save the Master even after, so I thought, making the Table the active workbook.

    I would love to maybe even have 1 button that from the Master that takes network/group info from user, opens Table, .Value=.Value, save as, copy/paste (.Value=.Value) into specific worksheet in Config workbook.

    That would be the ideal easy process.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: isEmpty(Cells(3, 8)) = True then not providing correct response until run 2nd time

    You will need to start a new thread for this as it's a completely different question.
    Cheers
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •