Home

Fixing vba code that replaces empty columns with dates.

%3CLINGO-SUB%20id%3D%22lingo-sub-741825%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-741825%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-note-subject%20lia-component-subject%22%3E%3CSPAN%3EI'm%20working%20on%20a%20VBA%20project%20and%20I%20have%20series%20of%20macro%20codes%20attached%20to%20an%20excel%20sheet%20that%20generates%20more%20excel%20sheets%20after.%20The%20project%20consists%20of%203%20stages%20and%20my%20issue%20has%20to%20do%20with%20the%203rd%20stage.%20The%20problem%20is%2C%20after%20the%20executing%20the%203rd%20stage%2C%20some%20blanks%20columns%20in%20the%20final%20generated%20report(%202%20particular%20columns%20to%20be%20precise)%20are%20automatically%20replaced%20with%20dates%20(exactly%2000.01.1900).%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-note-body%20lia-component-body%22%3E%3CP%3EIs%20there%20a%20way%20I%20can%20know%20which%20portion%20of%20the%20code%20replaces%20the%20empty%20columns%20with%20dates%3F%20I%20want%20the%20blank%20columns%20to%20remain%20the%20same%20(P.S%3A%20I%20just%20started%20getting%20familiar%20with%20VBA%20this%20week).%3CBR%20%2F%3EI%20have%20to%20tried%20to%20guess%20where%20the%20issue%20happens%20and%20I%20have%20posted%20some%20codes%20below%20which%20i%20think%20might%20be%20from%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20cpt%20%3D%202%20To%20len_l%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_crea_avis).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_crea_avis%20%2B%201).Value%20%3D%20Year(ws_ta.Cells(cpt%2C%20col_crea_avis).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_crea_avis).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_crea_avis%20%2B%202).Value%20%3D%20Month(ws_ta.Cells(cpt%2C%20col_crea_avis).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_crea_avis).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_crea_avis%20%2B%203).Value%20%3D%20AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt%2C%20col_crea_avis).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_crea_tache).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_crea_tache%20%2B%201).Value%20%3D%20Year(ws_ta.Cells(cpt%2C%20col_crea_tache).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_crea_tache).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_crea_tache%20%2B%202).Value%20%3D%20Month(ws_ta.Cells(cpt%2C%20col_crea_tache).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_crea_tache).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_crea_tache%20%2B%203).Value%20%3D%20AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt%2C%20col_crea_tache).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_ter_tache).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_ter_tache%20%2B%201).Value%20%3D%20Year(ws_ta.Cells(cpt%2C%20col_ter_tache).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_ter_tache).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_ter_tache%20%2B%202).Value%20%3D%20Month(ws_ta.Cells(cpt%2C%20col_ter_tache).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20ws_ta.Cells(cpt%2C%20col_ter_tache).Value%20Like%20%22%22%20Then%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20col_ter_tache%20%2B%203).Value%20%3D%20AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt%2C%20col_ter_tache).Value)%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3Ews_ta.Cells(cpt%2C%20len_d%20%2B%202).Value%20%3D%20str_date%3CBR%20%2F%3ENext%20cpt%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elen_d%20%3D%20ws_qn.Cells(1%2C%20len_col_max).End(xlToLeft).Column%3CBR%20%2F%3Ews_qn.Activate%3CBR%20%2F%3Earea%20%3D%20AGEN_Data.ConvertToLetter(len_d%20%2B%206)%20%26amp%3B%20%222%3A%22%20%26amp%3B%20AGEN_Data.ConvertToLetter(len_d%20%2B%206)%20%26amp%3B%20len_l%3CBR%20%2F%3Ews_qn.Cells(2%2C%20len_d%20%2B%206)%20%3D%20str_date%3CBR%20%2F%3Ews_qn.Cells(2%2C%20len_d%20%2B%206).AutoFill%20Destination%3A%3DRange(area)%2C%20Type%3A%3DxlFillCopy%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-741825%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-746968%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-746968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371611%22%20target%3D%22_blank%22%3E%40Fatman003%3C%2FA%3EI%20expect%20the%20issue%20is%20with%20this%20line%3A%3C%2FP%3E%0A%3CP%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ws_ta.Cells(cpt%2C%20col_ter_tache%20%2B%203).Value%20%3D%20AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt%2C%20col_ter_tache).Value)%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EIf%20the%20function%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23333333%3B%20font-family%3A%20%26amp%3Bquot%3B%20segoeui%26amp%3Bquot%3B%2C%26amp%3Bquot%3Blato%26amp%3Bquot%3B%2C%26amp%3Bquot%3Bhelvetica%20neue%26amp%3Bquot%3B%2Chelvetica%2Carial%2Csans-serif%3B%20font-size%3A%2016px%3B%20font-style%3A%20normal%3B%20font-variant%3A%20normal%3B%20font-weight%3A%20300%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-decoration%3A%20none%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20-webkit-text-stroke-width%3A%200px%3B%20white-space%3A%20normal%3B%20word-spacing%3A%200px%3B%22%3EAAA_TOOL_QN_Lib.DetermineWeek%3C%2FFONT%3Ereturns%20a%20zero%20value%2C%26nbsp%3B%3C%2FFONT%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3Eor%20with%20this%20one%3A%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ws_ta.Cells(cpt%2C%20len_d%20%2B%202).Value%20%3D%20str_date%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3Eif%20str_date%20equals%20zero.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Fatman003
Occasional Contributor
I'm working on a VBA project and I have series of macro codes attached to an excel sheet that generates more excel sheets after. The project consists of 3 stages and my issue has to do with the 3rd stage. The problem is, after the executing the 3rd stage, some blanks columns in the final generated report( 2 particular columns to be precise) are automatically replaced with dates (exactly 00.01.1900).

Is there a way I can know which portion of the code replaces the empty columns with dates? I want the blank columns to remain the same (P.S: I just started getting familiar with VBA this week).
I have to tried to guess where the issue happens and I have posted some codes below which i think might be from there.

 

For cpt = 2 To len_l
If Not ws_ta.Cells(cpt, col_crea_avis).Value Like "" Then
ws_ta.Cells(cpt, col_crea_avis + 1).Value = Year(ws_ta.Cells(cpt, col_crea_avis).Value)
End If

If Not ws_ta.Cells(cpt, col_crea_avis).Value Like "" Then
ws_ta.Cells(cpt, col_crea_avis + 2).Value = Month(ws_ta.Cells(cpt, col_crea_avis).Value)
End If

If Not ws_ta.Cells(cpt, col_crea_avis).Value Like "" Then
ws_ta.Cells(cpt, col_crea_avis + 3).Value = AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt, col_crea_avis).Value)
End If

If Not ws_ta.Cells(cpt, col_crea_tache).Value Like "" Then
ws_ta.Cells(cpt, col_crea_tache + 1).Value = Year(ws_ta.Cells(cpt, col_crea_tache).Value)
End If

If Not ws_ta.Cells(cpt, col_crea_tache).Value Like "" Then
ws_ta.Cells(cpt, col_crea_tache + 2).Value = Month(ws_ta.Cells(cpt, col_crea_tache).Value)
End If

If Not ws_ta.Cells(cpt, col_crea_tache).Value Like "" Then
ws_ta.Cells(cpt, col_crea_tache + 3).Value = AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt, col_crea_tache).Value)
End If

If Not ws_ta.Cells(cpt, col_ter_tache).Value Like "" Then
ws_ta.Cells(cpt, col_ter_tache + 1).Value = Year(ws_ta.Cells(cpt, col_ter_tache).Value)
End If

If Not ws_ta.Cells(cpt, col_ter_tache).Value Like "" Then
ws_ta.Cells(cpt, col_ter_tache + 2).Value = Month(ws_ta.Cells(cpt, col_ter_tache).Value)
End If

If Not ws_ta.Cells(cpt, col_ter_tache).Value Like "" Then
ws_ta.Cells(cpt, col_ter_tache + 3).Value = AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt, col_ter_tache).Value)
End If

ws_ta.Cells(cpt, len_d + 2).Value = str_date
Next cpt

 

Or this:

 

len_d = ws_qn.Cells(1, len_col_max).End(xlToLeft).Column
ws_qn.Activate
area = AGEN_Data.ConvertToLetter(len_d + 6) & "2:" & AGEN_Data.ConvertToLetter(len_d + 6) & len_l
ws_qn.Cells(2, len_d + 6) = str_date
ws_qn.Cells(2, len_d + 6).AutoFill Destination:=Range(area), Type:=xlFillCopy

1 Reply

@Fatman003 I expect the issue is with this line:

            ws_ta.Cells(cpt, col_ter_tache + 3).Value = AAA_TOOL_QN_Lib.DetermineWeek(ws_ta.Cells(cpt, col_ter_tache).Value)

If the function AAA_TOOL_QN_Lib.DetermineWeek returns a zero value, or with this one:

        ws_ta.Cells(cpt, len_d + 2).Value = str_date

if str_date equals zero.

Related Conversations