Home

Fixing vba code that replaces empty columns with dates.

%3CLINGO-SUB%20id%3D%22lingo-sub-741872%22%20slang%3D%22en-US%22%3EFixing%20vba%20code%20that%20replaces%20empty%20columns%20with%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741872%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-note-subject%20lia-component-subject%22%3E%3CP%3EI'm%20working%20on%20some%20VB%20projects%20that%20has%20to%20do%20with%20generation%20of%20new%20excel%20sheets%20and%20some%20data%20analysis.%20In%20the%20final%20sheet%20generated%2C%20there%20are%20some%20columns%20that%20contain%20excel%20default%20dates%20(00.01.1900)%3C%2FP%3E%3CP%3EI%20later%20figured%20out%20there%20is%20a%20section%20of%20the%20code%20that%20converts%20blank%20cells%20from%20a%20previous%20sheet%20into%20the%20default%20dates%20in%20the%20final%20sheet.%20Here%20is%20some%20portion%20of%20the%20code%20I%20figured%20is%20the%20problem%3C%2FP%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-note-body%20lia-component-body%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122528iD0823BB30F4F9606%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22vb.PNG%22%20title%3D%22vb.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%3ESo%20what%20can%20I%20can%20change%20(I'm%20guessing%20its%20from%20the%20last%205%20lines)%20from%20this%20code%20that%20would%20leave%20the%20empty%20cells%20in%20the%20prvs%20sheet%20to%20remain%20the%20same%20in%20the%20final%20sheet.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-741872%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-742525%22%20slang%3D%22en-US%22%3ERe%3A%20Fixing%20vba%20code%20that%20replaces%20empty%20columns%20with%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742525%22%20slang%3D%22en-US%22%3EThe%20problem%20is%20with%20the%20line%20where%20you%20add%20two%20cells%20together.%20If%20those%20cells%20are%20empty%2C%20the%20addition%20(%2B%20sign)%20forces%20Excel%20to%20use%20the%20default%20numerical%20value%20of%20an%20empty%20cell%2C%20which%20is%20zero.%20SO%20the%20result%20of%20adding%20two%20empty%20cells%20is%20always%20a%20zero.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-749495%22%20slang%3D%22en-US%22%3ERe%3A%20Fixing%20vba%20code%20that%20replaces%20empty%20columns%20with%20dates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-749495%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F20745%22%20target%3D%22_blank%22%3E%40Jan%3C%2FA%3EThanks!%20I%20was%20finally%20able%20to%20fix%20it%20by%20removing%20the%20CDate%20function%20from%20the%20ws.Cells%20and%20equated%20the%20variable%20to%20itself!%3C%2FLINGO-BODY%3E
Fatman003
Occasional Contributor

I'm working on some VB projects that has to do with generation of new excel sheets and some data analysis. In the final sheet generated, there are some columns that contain excel default dates (00.01.1900)

I later figured out there is a section of the code that converts blank cells from a previous sheet into the default dates in the final sheet. Here is some portion of the code I figured is the problem

vb.PNGSo what can I can change (I'm guessing its from the last 5 lines) from this code that would leave the empty cells in the prvs sheet to remain the same in the final sheet.

2 Replies
The problem is with the line where you add two cells together. If those cells are empty, the addition (+ sign) forces Excel to use the default numerical value of an empty cell, which is zero. SO the result of adding two empty cells is always a zero.
@Jan Thanks! I was finally able to fix it by removing the CDate function from the ws.Cells and equated the variable to itself!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies