+ Reply to Thread
Results 1 to 2 of 2

Add value to table based on date and other value lookup

Hybrid View

  1. #1
    Registered User
    Join Date
    04-05-2016
    Location
    India
    MS-Off Ver
    2013
    Posts
    77

    Add value to table based on date and other value lookup

    My objective is to write data on to a table based on lookup of two field viz. “Date” and “Part & SlNo”. I have a code linked to the “ADD” button. I referred the code which is almost similar to that of my requirement.

    https://www.get-digital-help.com/add...o-a-table-vba/

    But the difference in my case is that I am handling dates (in dd/mm/yy format). Hence, I am unable to get over the error in comparison of date. I used the following code.

    Sub AddVal()
    Dim column, row As String
    Dim c, r As Single
    With Worksheets("data")
    If .Range("B1").Value = "" Or .Range("B2").Value = "" Then Exit Sub
    column = .Range("B1").Value
    row = .Range("B2").Value
    c = Application.Match(column, .Range("D5:U5"), 0)
    r = Application.Match(row, .Range("C6:C16"), 0)
    .Range("C5").Offset(r, c).Value = .Range("B3").Value
    End With
    End Sub

    Please help. Workbook attached
    Attached Files Attached Files
    Last edited by melvinkoshy; 05-21-2020 at 02:01 PM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Cool Hi ! Try this !


    Your code is near to be acceptable but
    you did not use the appropriate cell property according to the date data type
    neither the appropriate variable data type
    - must be Variant (like C but not R) as MATCH worksheet function can return an error instead of an integer -
    and 'cause this error may raise you forgot to check the result of this function …

    Your procedure revamped according to the above and the cells validation :

    PHP Code: 
    Sub AddVal()
        
    Dim A$, CR
        With Sheet1
            A 
    = .[B2].Validation.Formula1
            C 
    Application.Match(.[B1].Value2, .Range(.[B1].Validation.Formula1), 0)
            
    Application.Match(.[B2].Text, .Range(A), 0)
            If 
    IsNumeric(C) And IsNumeric(RThen .Range(A)(0).Offset(RC).Value = .[B3].Value Else Beep
        End With
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » ! ◄ ◄
    Last edited by Marc L; 05-21-2020 at 10:33 PM. Reason: simplifying …

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Match Closest Date in a table but the date can't be after the lookup value
    By excelmelucy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2019, 06:05 AM
  2. [SOLVED] Lookup formla to allocate a calendar date to a week based on a table
    By shrijan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-08-2016, 02:42 AM
  3. [SOLVED] Lookup latest date in a table based on 2 criteria
    By mariannehislop in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-29-2015, 04:21 AM
  4. Replies: 1
    Last Post: 12-19-2014, 02:08 PM
  5. [SOLVED] Collate (lookup) Multiple orders at the same time, based on DATE range in a table
    By Prodschdler in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-05-2013, 05:19 PM
  6. Replies: 2
    Last Post: 09-20-2010, 02:51 PM

Tags for this Thread

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