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

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
203
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,
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,943
Office Version
365
Platform
Windows
Try
Code:
Ws.Range("D3:H52").Value = Ws.Range("D3:H52").Value
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
203
I am guessing that is instead of the .PasteSpecial Paste:=xlPasteValues
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,943
Office Version
365
Platform
Windows
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
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
203
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,943
Office Version
365
Platform
Windows
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.
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
203
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,943
Office Version
365
Platform
Windows
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.
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
203
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,943
Office Version
365
Platform
Windows
You will need to start a new thread for this as it's a completely different question.
Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,102,843
Messages
5,489,216
Members
407,679
Latest member
Elaine Grass

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top