Another Macro Problem

T

tinamiller1

New member
Joined
Aug 28, 2013
Messages
16
Reaction score
0
Points
0
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
 

Attachments

  • example.xlsx
    10.1 KB · Views: 16
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
 
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
 
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.
 
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
 
You must log in or register to reply here.
Back
Top