jeudi 30 janvier 2020

Using existing values exported from SAP to populate new column values through VBA

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