Results 1 to 5 of 5

Thread: Another Macro Problem

  1. #1

    Another Macro Problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I attached an example of my data:

    I am now on another workbook and have a scenario where I need to copy another name to existing cells and make it so the same name is filled in the blank cells. The following macro I can use to fill cells but each cell has to have data.


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

    This will not work for what I need to accomplish with this particular workbook. I have an example of my data have and what I need. Now, I have over 3000 rows with this particular sheet and doing a click and drag to get all the AIPS filled in the cells and all the standards filled in the cells and using the above macro will work, however, I have workbooks that come over like this once a week and creating this macro for someone that does not know anything about Excel and the PM wants an automated process to help them out. So, the above macro will work however not exactly. As you see from below, AIP is for the claim 111, 222, 333, 777, so the AIP along with those blank cells needs to say Implant Pass-through: Auto Invoice Pricing (AIP) and 444, 555, 666 need to say
    Implant Pass-through: PPR Tied to Invoice
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Moved your needed data over to the right of the data you have and then used this macro.
    Not the most concise but easy to follow and modify, hope it is of some assistance.

    Code:
    Sub imptype()
    'to fill in IMPTYP from CLM number
    
        Dim CLMrng As Range      'the range of cells to work with
        Dim cel As Range         'the individual cell in CLMrng
        Dim LastRow As Long      'the last row used in the CLMrng
        Dim c As String          'the value in the cell being worked with
        
    'to avoid slow down from screen updating
        Application.ScreenUpdating = False
    
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    
    Set CLMrng = Sheets("Sheet1").Range("A3:A" & LastRow)
    
    For Each cel In CLMrng
    
        c = Trim(cel.Value)
        
        If c = "111" Then
            cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
        ElseIf c = "222" Then
            cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
        ElseIf c = "333" Then
            cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
        ElseIf c = "444" Then
            cel.Offset(0, 3).Value = "Implant Pass-through: PPR Tied to Invoice"
        ElseIf c = "555" Then
            cel.Offset(0, 3).Value = "Implant Pass-through: PPR Tied to Invoice"
        ElseIf c = "666" Then
            cel.Offset(0, 3).Value = "Implant Pass-through: PPR Tied to Invoice"
        ElseIf c = "777" Then
            cel.Offset(0, 3).Value = "Implant Pass-through: Auto Invoice Pricing (AIP)"
        End If
        
    Next cel
    
        Application.ScreenUpdating = True
        
    End Sub

  3. #3
    Thanks, but this was just an example of the type of data. I actually have 3000 rows with 580 diff claim numbers. So naming the claims 580 times would just not work. I will probably just go back to manually filling in the blank AIP and Standards between each one and then run the macro to change all AIP's and Standards to be what they should. I am not sure why I cannot use the methodology behind this macro and just apply but I cannot figure out the if portion right:


    'macro thirteen
    'fill total invoice cells
    Sub FillCell2()
    Dim c As Range
    Set MyRange = Range("t2:t935,u2:u935,v2:v935") '***** Define your data range

    For Each c In MyRange
    If c.Value = "" Then c.Value = c.Offset(-1, 0).Value
    Next c
    End Sub

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    The 3000 rows I saw. The 580 different claim numbers aren't mentioned in your post or example.

    From the two requests for assistance that you have posted it appears you are looking at/for two different ways to get the same information into the same cells, but what are you starting with?

    More/better information and a realistic example would go a long way in assisting with what you are actually working with and trying to do.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    After seeing your posts at MrExcel and ExcelFox I realize what you are now asking really has nothing to do with the claim numbers. That part of things has to have been dealt with prior to getting to this point and you now want to work with the results that currently exist in the IMPTYP column.

    I have no idea what you are doing with the sorting or grouping of claim numbers nor how you guarantee that the first line in the group will have "AIP" or "Standard" and not be blank (ie: rows 3 and 4 of your example being swapped). That being said, this macro should do what you have asked.

    Code:
    Sub ImpType2()
    
        Dim MyRange As Range        'the range to work on
        Dim cel As Range            'the cell in MyRange being worked on
        Dim PhraseToEnter As String 'what is to be put into the cell
        
    Set MyRange = Range("D3:D20")   'what ever the range actually is
    
    For Each cel In MyRange
        If Trim(cel.Value) = "AIP" Then
            PhraseToEnter = "Implant Pass-through: Auto Invoice Pricing (AIP)"
        ElseIf Trim(cel.Value) = "Standard" Then
            PhraseToEnter = "Implant Pass-through: PPR Tied to Invoice"
        ElseIf Trim(cel.Value) = "" Then
            PhraseToEnter = PhraseToEnter
        End If
        cel.Value = PhraseToEnter
    Next cel
        
    End Sub

Posting Permissions

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