usage of WorksheetFunction.Year in excel vba For loop

acr

I have a series of dates in my sheet DailyData A column. I need to find out whether its leap year (in A Column) and depends upon that performing a calculation in F Column.( for leap year, I need to use 366 else 365)

Found that using below formula, I can check its leap year or not.

b = 4 a=Year to check

If a - (b * (a \ b)) = 0, then its leap year

Now, I am using below code to perform the calculation, but its giving me error "Object doesn't support this property or method" on line a = WorksheetFunction.Year("A", J)

What will be correct method of check it ?

Sub DayTest()
    Dim MaxGain As Workbook
    Dim Main As Worksheet
    Dim DailyData As Worksheet
    Dim n As Long
    Dim J As Long
    Dim a As String, b As Integer

    Set MaxGain = Excel.Workbooks("MaxGain.xlsm")
    Set DailyData = MaxGain.Worksheets("DailyData")
    Set Main = MaxGain.Worksheets("Main")
    DailyData.Activate


    n = DailyData.Cells(Rows.Count, "A").End(xlUp).Row

    J = 1
    b = 4

    For J = 2 To n

    a = WorksheetFunction.Year("A", J)

    If a - (b * (a \ b)) = 0 Then

    DailyData.Range("F" & J).Value = Main.Range("B2").Value / 366

    Else

    DailyData.Range("F" & J).Value = Main.Range("B2").Value / 365

    End If

    Next


End Sub

UPDATE: I have figured it. Changed the line

a = WorksheetFunction.Year("A", J)

to

a = Year(DailyData.Range("A" & J))
acr

I have figured it.Changed below line

a = WorksheetFunction.Year("A", J)

to

a = Year(DailyData.Range("A" & J))

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"

From Dev

excel vba : selected cells loop

From Dev

Excel VBA For Each Worksheet Loop

From Dev

Excel VBA WorksheetFunction.IsError false positive

From Dev

Excel VBA: Error 1004 WorkSheetFunction 'Unable to get Vlookup property"

From Dev

Extract Year from Date Using VBA in Excel

From Dev

Loop Without Do, Extracting Year From Date in Excel With VBA

From Dev

VBA EXCEL: Use value in Listbox as Lookup value in the Match WorksheetFunction

From Dev

excel vba compile error on year function

From Dev

Searching for function usage in Excel VBA

From Dev

WorksheetFunction.Match multiple sheets Excel VBA error 1004

From Dev

Find and replace loop in excel vba

From Dev

For loop and If statement Excel VBA

From Dev

For loop in excel VBA

From Dev

VBA WorksheetFunction dynamic randbetween

From Dev

Trouble with WorksheetFunction in Excel VBA

From Dev

VBA Excel Transform to loop

From Dev

Excel VBA worksheetfunction.transpose returns dimensions but no values

From Dev

VBA or Excel Macro to add 1year

From Dev

Application.worksheetFunction.match not working correctly VBA Excel

From Dev

application.worksheetfunction.vlookup in vba does not loop

From Dev

VBA Excel WorksheetFunction.Rank issue

From Dev

Number of days in a year Excel VBA

From Dev

EXCEL VBA - how to pass # of filled cells inside a custom range, into the "i" variable using Application.WorksheetFunction.CountA?

From Dev

Loop formula in excel vba

From Dev

VBA & Excel - Loop eecordset

From Dev

Excel vba percentile worksheetfunction function with collection argument

From Dev

EXCEL VBA - LOOP error

From Dev

Excel VBA Loop?

Related Related

  1. 1

    Excel VBA: Can't get a match, error "Unable to get the Match property of the WorksheetFunction class"

  2. 2

    excel vba : selected cells loop

  3. 3

    Excel VBA For Each Worksheet Loop

  4. 4

    Excel VBA WorksheetFunction.IsError false positive

  5. 5

    Excel VBA: Error 1004 WorkSheetFunction 'Unable to get Vlookup property"

  6. 6

    Extract Year from Date Using VBA in Excel

  7. 7

    Loop Without Do, Extracting Year From Date in Excel With VBA

  8. 8

    VBA EXCEL: Use value in Listbox as Lookup value in the Match WorksheetFunction

  9. 9

    excel vba compile error on year function

  10. 10

    Searching for function usage in Excel VBA

  11. 11

    WorksheetFunction.Match multiple sheets Excel VBA error 1004

  12. 12

    Find and replace loop in excel vba

  13. 13

    For loop and If statement Excel VBA

  14. 14

    For loop in excel VBA

  15. 15

    VBA WorksheetFunction dynamic randbetween

  16. 16

    Trouble with WorksheetFunction in Excel VBA

  17. 17

    VBA Excel Transform to loop

  18. 18

    Excel VBA worksheetfunction.transpose returns dimensions but no values

  19. 19

    VBA or Excel Macro to add 1year

  20. 20

    Application.worksheetFunction.match not working correctly VBA Excel

  21. 21

    application.worksheetfunction.vlookup in vba does not loop

  22. 22

    VBA Excel WorksheetFunction.Rank issue

  23. 23

    Number of days in a year Excel VBA

  24. 24

    EXCEL VBA - how to pass # of filled cells inside a custom range, into the "i" variable using Application.WorksheetFunction.CountA?

  25. 25

    Loop formula in excel vba

  26. 26

    VBA & Excel - Loop eecordset

  27. 27

    Excel vba percentile worksheetfunction function with collection argument

  28. 28

    EXCEL VBA - LOOP error

  29. 29

    Excel VBA Loop?

HotTag

Archive