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

[tinamiller1]

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




'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)

[patel]

your code works well on my test sheet

[tinamiller1]

It is not working for me. It labels everything as Implant Pass-through: PPR Tied to Invoice

[alansidman]

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.

[bakerman]

@ 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.

Sub tst()
MsgBox IIf(1 < 2, "Yes", "No")
MsgBox IIf(2 < 1, "Yes", "No")
End Sub

[Admin]

Hi

Another option


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

[Rick Rothstein]

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.


' 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



' 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

[Excel Fox]

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


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

[tinamiller1]

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.

[alansidman]

@excel fox and bakerman

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

[Admin]

Try


Sub imptype()

On Error Resume Next
With Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row)
.SpecialCells(4).FormulaR1C1 = "=r[-1]c"
.Value2 = .Value2
End With

End Sub