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

LlebKcir

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

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try
Code:
Ws.Range("D3:H52").Value = Ws.Range("D3:H52").Value
 
Upvote 0
I am guessing that is instead of the .PasteSpecial Paste:=xlPasteValues
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You will need to start a new thread for this as it's a completely different question.
Cheers
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top