Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA Macro To Fill Missing Items In A Sequence Of Values Based On Criteria

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0

    VBA Macro To Fill Missing Items In A Sequence Of Values Based On Criteria

    I am trying to change the cells in my spreadsheet and created a macro but it is not working quite right. I had to update this because I confused the person that responded and hope other will view this because my macro is still not working. I might have to repost. There are blanks between the data have. So, I need to read the first item and change that cell as well as the following cells until I reach the next item. If that item is like, it will continue. If not, then it will paste the other label and fill in the blanks with that until it gets to the next. There are only 2. I hope this makes sense.

    Macro

    Code:
    'macro ten
    'implant update
    
    Sub imptype()
    Dim c As Range
    For Each c In Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row)
        c = IIf(UCase(c) = "AIP", "Implant Pass-through: Auto Invoice Pricing (AIP)", "Implant Pass-through: PPR Tied to Invoice")
    Next
    End Sub





    DATA Have
    AIP






    Standard




    AIP





    Standard




    AIP




    DATA Need
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: PPR Tied to Invoice
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Implant Pass-through: Auto Invoice Pricing (AIP)
    Last edited by tinamiller1; 08-28-2013 at 10:37 PM.

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    your code works well on my test sheet

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    It is not working for me. It labels everything as Implant Pass-through: PPR Tied to Invoice

  4. #4
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    This appears to be an Excel issue, but you have posted in the Access Forum. Is this in fact an excel issue? What is your issue? What results are you getting? It appears that you are using the syntax for Access which is IIF versus the syntax for Excel which is IF.
    Last edited by alansidman; 08-29-2013 at 09:01 AM.

  5. #5
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    @ alansidman

    IIf is in fact an XL-function. It's a sort of short version of If...Then...Else. It has 3 parts (expr,truepart,falsepart)
    In the 1st you evaluate a certain expression, if true returns truepart, if false it returns falsepart.
    F.e.
    Code:
    Sub tst()
        MsgBox IIf(1 < 2, "Yes", "No")
        MsgBox IIf(2 < 1, "Yes", "No")
    End Sub

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Another option

    Code:
    Sub imptype()
    
        Dim c   As Range
        Dim a   As String
        
        Set c = Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row)
        a = c.Address
        
        c = Evaluate("=if(" & a & "=""aip"",""Implant Pass-through: Auto Invoice Pricing (AIP)"",""Implant Pass-through: PPR Tied to Invoice"")")
    
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Here are two more options... the first one is mostly to show yet another alternative, but I seriously doubt you will find a faster method than the second one.

    Code:
    ' Interesting alternative
    '
    Sub ImpType1()
      With Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
        .Replace "AIP", "=AIP", xlWhole
        .SpecialCells(xlConstants).Clear
        .SpecialCells(xlBlanks).Value = "Implant Pass-through: PPR Tied to Invoice"
        .SpecialCells(xlFormulas).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
      End With
    End Sub
    Code:
    ' Super fast method
    '
    Sub ImpType2()
      Dim X As Long, vArr As Variant
      vArr = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
      For X = 1 To UBound(vArr)
        If vArr(X, 1) = "AIP" Then
          vArr(X, 1) = "Implant Pass-through: Auto Invoice Pricing (AIP)"
        Else
          vArr(X, 1) = "Implant Pass-through: PPR Tied to Invoice"
        End If
      Next
      Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row) = vArr
    End Sub

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Just to clarify on the point about IIF (also known as the Immediate If) versus If...Else...End If

    IIF has to evaluate the entire function before returning a result whether the TRUE part or the FALSE part. If the following code is tested, you'll easily figure it out

    Code:
    Sub IIFVsIFTest()
    
        Dim lng As Long
        lng = 0
        MsgBox IIf(lng = 0, "Please do not use zero", 1 / lng) 'Throws division by zero error
        If lng = 0 Then
            MsgBox "Please do not use zero"
        Else
            MsgBox 1 / lng
        End If
        
    End Sub
    So for posterity, IIF and If...Else...End If cannot be substitutes for each other, unless the developer clearly knows what the code does
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Junior Member
    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Ok, these options somewhat work. But, I guess I am not explaining the data have correctly. I have either AIP or Standard and then blank cells in between. So, if I have AIP on J1 and then Standard on J15, the Implant Pass-through: Auto Invoice Pricing (AIP) needs to be on J1 through J14. If the Standard is J15 and the next AIP is J20, then Implant Pass-through: PPR Tied to Invoice needs to be J15 to J19 and so on.

    This is because there are other items in this Excel. I have the claim number, component information. So the full raw data would be like this:

    clm component manu imptype
    111 xyxy xyxy AIP
    111 2222 djrkf
    111 jones johnson
    111 xyxy fjklda
    222 fjdk fjdskl Standard
    222 fjsk fjdksal
    222 fjsdk fdwjkf
    333 fjk fjklas AIP
    333 fsdjkl fjksla
    444 fjk fjdsk AIP
    444 fjdskl djak
    555 jk jjkl AIP
    555 jkl jlj
    666 jk jklj Standard
    666 jk jkljl


    So, claim 111 is all an AIP claim and I need it to fill the correct AIP wording. Claim 222 is a standard and that needs to be filled in. claim 333, 444, 555, 666 and so on works the same.
    Last edited by tinamiller1; 08-29-2013 at 06:34 PM. Reason: update

  10. #10
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    @excel fox and bakerman

    Thanks for the explanation on the differences. Great to learn new stuff!

Similar Threads

  1. Replies: 1
    Last Post: 08-20-2013, 04:31 PM
  2. Replies: 4
    Last Post: 04-05-2013, 12:08 PM
  3. Sum values based on multiple criteria
    By Jorrg1 in forum Excel Help
    Replies: 8
    Last Post: 01-07-2013, 03:04 PM
  4. Macro to clear data based on color fill
    By Howardc in forum Excel Help
    Replies: 7
    Last Post: 12-03-2012, 09:25 AM
  5. Fetch multiple values based on criteria
    By Lucero in forum Excel Help
    Replies: 8
    Last Post: 04-07-2012, 12:35 PM

Posting Permissions

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