Fixing vba code that replaces empty columns with dates.

Copper 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.