+ Reply to Thread
Results 1 to 9 of 9

Apply Formula Uptill Last Row

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    909

    Apply Formula Uptill Last Row

    Hello Again,

    I am seeking for a vba macros on the below mentioned details.

    I am trying to apply a formula in comuln G uptill last row based on column A. Means if column A have data till row no. 17 then the fomula will be implemented till row 17 in column G.

    I hope it is clear mentioned details for seeking help from a expert user.

    Copy of a sample file is attached for your understanding.
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Apply Formula Uptill Last Row

    Here is a sample


    Sub FillDown()
        Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("G2:G" & LR).Formula = "=Today()"
    End Sub
    HTH
    Regards, Jeff

  3. #3
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    909

    Re: Apply Formula Uptill Last Row

    Thanks for your reponse Mr. Jeff,

    I am applying formula through vba macros recording something like this

    Sub FillDown()
        Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("G2:G" & LR).Formula = "=IF(AND(RC[-5]<>""M"",RC[-5]<>""F""), ""Only 1 letter (M or F) is allowed as gender"", IF(NOT(OR(RC[-4]=""VIP"",RC[-4]=""A"",RC[-4]=""B"",RC[-4]=""C"")), ""Only VIP, A, B & C is allowed in class"", IF(NOT(AND(ISNUMBER(RC[-3]), RC[-3]>=0, RC[-3]<=999)), ""Only 3 characters or numeric values are allowed in Age"", IF(NOT(ISNUMBER(SEARCH(RC[-2], ""EMPLOYEESPOUSECHILD"")" & "Employee, Spouse or child is allowed in this column"", IF(NOT(ISNUMBER(SEARCH(RC[-1], ""African, Asian, Middle East, Saudi, Other""))), ""African, Asian, Middle East, Saudi & Other is allowed in this column"", """")))))"
        '"=Today()"
    End Sub
    But it is giving me an error saying "Application-defined or object-defined error".

    ---------- Post added at 07:04 AM ---------- Previous post was at 07:00 AM ----------

    Just now i have tested my formula though vba recording it is displaying the same error.

    
    FORMULA:
    
    =IF(AND(B2<>"M",B2<>"F"), "Only 1 letter (M or F) is allowed as gender", IF(NOT(OR(C2="VIP",C2="A",C2="B",C2="C")), "Only VIP, A, B & C is allowed in class", IF(NOT(AND(ISNUMBER(D2), D2>=0, D2<=999)), "Only 3 characters or numeric values are allowed in Age", IF(NOT(ISNUMBER(SEARCH(E2, "EMPLOYEESPOUSECHILD"))), "Employee, Spouse or child is allowed in this column", IF(NOT(ISNUMBER(SEARCH(F2, "African, Asian, Middle East, Saudi, Other"))), "African, Asian, Middle East, Saudi & Other is allowed in this column", "")))))

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Apply Formula Uptill Last Row

    The formula you have posted within the snippet of code is in R1C1

    So, replace
    .Formula
    with
    .FormulaR1C1
    Because this formula is so long I broke it into multiple rows
    Sub FillDown()
        Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("G2:G" & LR).FormulaR1C1 = "=IF(AND(RC[-5]<>""M"",RC[-5]<>""F""), ""Only 1 letter (M or F) is allowed as gender""," & Chr(10) & " " & _
        "IF(NOT(OR(RC[-4]=""VIP"",RC[-4]=""A"",RC[-4]=""B"",RC[-4]=""C"")), ""Only VIP, A, B & C is allowed in class""," & Chr(10) & " " & _
        "IF(NOT(AND(ISNUMBER(RC[-3]), RC[-3]>=0, RC[-3]<=999)), ""Only 3 characters or numeric values are allowed in Age""," & Chr(10) & " " & _
        "IF(NOT(ISNUMBER(SEARCH(RC[-2], ""EMPLOYEESPOUSECHILD""))), ""Employee, Spouse or child is allowed in this column""," & Chr(10) & " " & _
        "IF(NOT(ISNUMBER(SEARCH(RC[-1], ""African, Asian, Middle East, Saudi, Other""))), ""African, Asian, Middle East, Saudi & Other is allowed in this column"", """")))))"
    End Sub
    Last edited by jeffreybrown; 08-10-2012 at 09:04 AM.

  5. #5
    Forum Contributor bonny24tycoon's Avatar
    Join Date
    04-02-2012
    Location
    Hell
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    405

    Re: Apply Formula Uptill Last Row

    This should work fine for you -


    Sub test()
    Dim x As Long
    x = Cells(Rows.Count, "a").End(xlUp).Row
    Range("G2").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(AND(RC[-5]<>""M"",RC[-5]<>""F""), ""Only 1 letter (M or F) is allowed as gender""," & Chr(10) & " IF(NOT(OR(RC[-4]=""VIP"",RC[-4]=""A"",RC[-4]=""B"",RC[-4]=""C"")), ""Only VIP, A, B & C is allowed in class""," & Chr(10) & " IF(NOT(AND(ISNUMBER(RC[-3]), RC[-3]>=0, RC[-3]<=999)), ""Only 3 characters or numeric values are allowed in Age""," & Chr(10) & " IF(NOT(ISNUMBER(SEARCH(RC[-2], ""EMPLOYEESPOUSECHILD""))), ""Employee, Spouse or child is allowed in this column""," & Chr(10) & " IF(NOT(ISNUMBER(SEARCH(RC[-1], ""African, Asian, Middle East, Saudi, Other""))), ""African, Asian, Middle East, Saudi & Other is allowed in this column"", """")))))"
    Range("g2").Resize(x - 1, 1).FillDown
    
    End Sub
    Last edited by bonny24tycoon; 08-10-2012 at 08:50 AM. Reason: Updated Code
    Thanks,

    Bonny Tycoon


  6. #6
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    909

    Re: Apply Formula Uptill Last Row

    Thanks Bonny.

    It works. I have one more request. If a user plugin the deails from column A to Column F the formula will execute automatically. Can it be possible.

  7. #7
    Forum Contributor bonny24tycoon's Avatar
    Join Date
    04-02-2012
    Location
    Hell
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    405

    Re: Apply Formula Uptill Last Row

    Yes, it is possible...

    Giveme a minute while i put this in a code...

  8. #8
    Forum Contributor bonny24tycoon's Avatar
    Join Date
    04-02-2012
    Location
    Hell
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    405

    Re: Apply Formula Uptill Last Row

    Here you go..

    I have added a module and a private sub.. You should be fine.. Apply Formula Uptill Last Row.xlsm

  9. #9
    Valued Forum Contributor
    Join Date
    01-11-2012
    Location
    Riyadh, K.S.A.
    MS-Off Ver
    Windows 11 with Excel 2013 & 2016
    Posts
    909

    Re: Apply Formula Uptill Last Row

    Thanks for your help. Topic marked as closed and reputation added.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1