知識社群Login
[Excel VBA] 週數轉換日期)
by 艾鍗學院, 2011-06-09 23:13, Views(5283)
''-----週數轉換日期---
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
  If CheckBox1.Value = True Then
 
  End
 
  End If
 

If Not Intersect(Range("B3:CA6"), Target) Is Nothing Then
    
       WeekNum = Cells(2, ActiveCell.Column)
      
      If Not Intersect(Range("3:3"), Target) Is Nothing Then    
       serial = 40544 + (WeekNum - 1) * 7    ' 40544 means 2011/1/1
    
      
       End If
     
        If Not Intersect(Range("4:4"), Target) Is Nothing Then 
       serial = 40544 + (WeekNum - 1) * 7 + 1      
       End If
       
  
      
       End If
      
      
       ''--------------
      
      sWeekDay = Weekday(serial, vbMonday)
         Select Case UCase(sWeekDay)
       Case Is = "1"
         lWeekDay = "星期一"
       Case Is = "2"
         lWeekDay = "星期二"
       Case Is = "3"
         lWeekDay = "星期三"
       Case Is = "4"
         lWeekDay = "星期四"
       Case Is = "5"
         lWeekDay = "星期五"
       Case Is = "6"
         lWeekDay = "星期六"
       Case Is = "7"
         lWeekDay = "星期日"
    End Select
       '---status--
        str1 = Year(serial) & "/" & Month(serial) & "/" & Day(serial) & " (" & lWeekDay & ")"
        Range("W23").Value = str1
        

       MsgBox str1
      
      
End If
         
End Sub