Posts by Debs_newbee

    I am trying to create a weekly timecard using a copy of data filtered from a worksheet table (40 rows 15 Columns) and pasted into the Timecard file, and then using that destination file to save as the new weekly timecard file for an individual. I'm missing something somewhere having tried so many different solutions, but I just cant get the copied data to paste, and now just at a loss on what to try next for a resolution.

    Any help greatly received.


    After many hours of reading and learning, and trying different forms of references, I'm still getting the same error. Can you solve this mystery for me please, and let me know exactly where I have gone wrong. Much appreciated.

    Code
    1. Private Sub Btn_Find_Member_Click()
    2. SetSwitch = True
    3. ReDim GetMemberData(0 To 21) As Variant
    4. Do Until SetSwitch = False
    5. If I_Mem_Num.Value > 0 Then
    6. MsgBox I_Mem_Num
    7. GetMemberData = Application.WorksheetFunction.XLookup(Me.I_Mem_Num, Application.Workbooks("PNH Form Design v7.xlsm").Worksheets("Sheet1").Range("A:A"), Application.Workbooks("PNH Form Design v7.xlsm").Worksheets("Sheet1").Range("A:V"))
    8. MsgBox GetMemberData
    9. SetSwitch = False
    10. Exit Do

    When I Run the code on the Update form, I fill in a member number from the worksheet, and click FIND I get the

    "Runtime error 1004: Unable to get the Xlookup property of the Worksheetfunction class". From what I read I thought perhaps it might be fields not formatted the as expected, but it appeared I had those correct. Any assistance or insight into what might be the problem would be greatly appreciated.

    The Form accepts member number input in text box. AddMemberForm formats and stores the Member number value in worksheet as numeric. (I_Mem_Num).

    This has been driving me crazy to try to fix or find solution. Any help greatly appreciated.


    Code - Module level


    I'm trying to reproduce the following worksheet formula in VBA code, but keep getting a "Compile error" Expected end of statement, where TYPE is highlighted.

    Would appreciate a solution and/or an explanation of where I'm going wrong. Thanks everyone.


    Column A B c d

    Member# Status Receipt # Date Paid


    Cell formula = =IF(CELL("Type",D2)="b","Not paid",IF(D2>=DATE(YEAR(NOW()),1,1),"Paid",IF(D2>=DATE(2019,10,1),"Paid",IF(D2<=DATE(YEAR(NOW())-2,1,1),"Inactive","Expired"))))


    VBA I tried = "=IF(CELL("Type",R[0]C[2])="b","Not paid",IF(R[0]C[2]>=DATE(YEAR(NOW()),1,1),"Paid",IF(R[0]C[2]>=DATE(2019,10,1),"Paid",IF(R[0]C[2]<=DATE(YEAR(NOW())-2,1,1),"Inactive","Expired"))))"