I am having trouble coming up with a VBA code creating a new column with values based on an existing column. The code I have is shown below. I tried to attach an image of the excel file I am working with, but I am new to this website so it will not let me. I am initially extracting an excel file from SAP, containing date values that are change every time the macro is run. 'Start Date' (Column J), represents the day that the activity started. From this SAP data column, I created a macro that created a new column (Column L) counting the number of days it has been since the start date. From that column, I am attempting to create three more columns.
1) A column that counts as a 1 if 6< Column L < 20 Days, and counts as a 0 for everything else
2) A column that counts as a 1 if Column L > 20 Days, and counts as a 0 for everything else
3) A column that counts as a 1 if Column L < 6 Days, and counts as a 0 for everything else
With ActiveSheet
ndt = Application.Range("D:D").Cells.SpecialCells(xlCellTypeConstants).Count 'Counts number of rows in column to see how many values there are
End With
With ActiveSheet
Set rngCategory = .Range(.Cells(2, 9), .Cells(ndt, 9)) 'setting up of array of start date column
End With
varCategory = rngCategory 'setting varCategory equal to the array made earlier
agedresults = varCategory 'setting agedresults equal to the array
For lngIdx = 1 To UBound(varCategory) 'For the number of rows in the array
startdate = varCategory(lngIdx, 1) 'start date equals the array
agedresults(lngIdx, 1) = DateDiff("d", startdate, Format(Now(), "MM/DD/YYYY")) 'aged results equals the difference in todays date and the start date value
Next lngIdx
unaged = agedresults
For lngIdx = 1 To UBound(varCategory)
If agedresults(lngIdx, 1) < 6 Then unaged(lngIdx, 1) = 1 Else unaged(lngIdx, 1) = 0
Next lngIdx
With ActiveSheet
Set rngResults = .Range(.Cells(2, 12), .Cells(ndt, 12))
.Cells(1, 12) = "Days in QM Lot"
Set agedcolumn = .Range(.Cells(2, 13), .Cells(ndt, 13))
.Cells(1, 13) = "Aged"
End With
rngResults = agedresults
agedcolumn = aged
For some reason, the days value is being populated (Column L), but the other three columns are being left blank (except for the header). Does anybody know why this would be the case? Is there something wrong with my if statement?
Aucun commentaire:
Enregistrer un commentaire