CODEWITHSUNDEEP
excelvbaexcel-formula
NAVEEN PRAKASH
NAVEEN PRAKASH

Reputation: 130

Converting Date in workweek in excel vba

Hi I am having a columns in which dates are present in yyyy-mm-dd h:mm:ss format. I want to convert them into workweeks.

I tried it like 

For DateCounter = 1 To UBound(All_Submitted_Dates)
    All_workweek(DateCounter) = WorksheetFunction.WeekNum(All_Submitted_Dates(DateCounter))
Next DateCounter

But I am getting subscript out of range exception. Can anybody tell me whats the error?

Upvotes: 0

Views: 100

Answers (1)

Tom
Tom

Reputation: 9898

Assigning an array from a range directly ends up resulting in a 2D array and not a 1D. To overcome this you can either reference it as such e.g. All_Submitted_Dates(DateCounter, 1) or Transpose your input.

If your input data is kept in a single column you just need to Transpose it once

With Application
    All_Submitted_Dates = .Transpose(Range("K2", Range("K1").End(xlDown)))
End With

However, if your data is in multiple columns you will need to double Transpose it to get the 1D array

With Application
    All_Submitted_Dates = .Transpose(.Transpose(Range("K2", Range("K1").End(xlDown))))
End With

You can then reference your array as you have done in the rest of your code

Update after comments I think your issue is due to a combination of things (however this may just be due to your haven't included the information in your question). Can you take a look at the code below.

I've declared both All_Submitted_Dates and All_WorkWeek as variants. I've then set the All_Submitted_Dates with an explicit sheet reference (Yours would have been taking from the ActiveSheet possibly causing errors) and also Transpos[e]ing the values so that the code is working with a 1D array. I've then ReDimed the All_WorkWeek array so that the size matches with All_Submitted_Dates. I've then used your For loop as you've written above. However, I would suggest to use Lbound(All_Submitted_Dates) instead of 1 although it shouldn't matter too much but is good practice.

You will need to update the With Sheet1 block with a reference to your sheet where your input Data is stored however, this in my tests, works as intended. 

Dim All_Submitted_Dates As Variant, All_WorkWeek As Variant
Dim DateCounter As Long
' Update with your sheet reference
With Sheet1
    All_Submitted_Dates = Application.Transpose(.Range(.Range("K2"), .Cells(.Rows.Count, "K").End(xlUp)))
End With

ReDim All_WorkWeek(LBound(All_Submitted_Dates) To UBound(All_Submitted_Dates))
For DateCounter = 1 To UBound(All_Submitted_Dates)
    All_WorkWeek(DateCounter) = WorksheetFunction.WeekNum(All_Submitted_Dates(DateCounter))
Next DateCounter

The issues I found where:

  • Array being declared as 2D and referenced as 1D
  • Weak Range references meaning code was referring to ActiveSheet possibly causing errors
  • Range selection using End(xlDown) but starting above the first cell, causing the Range to only select one cell (therefore possibly defaulting to value of cell rather than creating an array)
  • All_WorkWeek possibly not initiated correctly so that it wouldn't accept any values

Upvotes: 1

Related Questions