Assistance with Looping Needed

[Philip1957]

Greetings,

I have some code that works, but but now I need to make it loop upwards through the blank cells and stop before overwriting the first non-blank cell. I just can't seem to figure out that whole "i = / Next i" thing.

Also, in the last "Else" statement if the cell (0,-13) from Active is blank I really don't want to write the string "Machine" in it. Is there a way to write an "Else Unless" statement?

Any assistance would be greatly appreciated.

Sub Assign_Category()

    Dim DfctCode As String
    
'    Select the Data worksheet  (Works)
    ActiveWorkbook.Sheets("Data").Select
    
'    Find the last Row  (Works)
    Selection.SpecialCells(xlCellTypeLastCell).Select
    
'    Paste category based on defect code  (Works)
    DfctCode = ActiveCell.Offset(0, -13)
            
            If InStr(DfctCode, "I") > 0 Then
            ActiveCell.Value = "Vendor"
            
            ElseIf InStr(DfctCode, "5") > 0 Or InStr(DfctCode, "22") > 0 _
            Or InStr(DfctCode, "26") > 0 Or InStr(DfctCode, "29") > 0 _
            Or InStr(DfctCode, "31") > 0 Then
            ActiveCell.Value = "Operator"
            
            ElseIf InStr(DfctCode, "I") > 0 Then
            ActiveCell.Value = "Vendor"

            Else: ActiveCell.Value = "Machine"
           
            End If
         
End Sub

Thank you for your time.

~ Phil

 

[Philip1957]

Thanks for the explanation. Every little bit of VBA knowledge helps.

I actually have a reference file in Word where I paste code and add notes using the comment tool meant for reviewing documents. Between that and my VBA for Dummies book, I am slowly getting better at coding.

~ Phil

 

[wrightyrx7]

Thanks for the explanation. Every little bit of VBA knowledge helps.

I actually have a reference file in Word where I paste code and add notes using the comment tool meant for reviewing documents. Between that and my VBA for Dummies book, I am slowly getting better at coding.

~ Phil

Very useful to know for people who use Microsoft office. When i first starting learning VBA i worked in payroll and pretty much automated all my work. Then i started building little applications in Access. I even built a few WinForm applications in Visual Studio because the coding language VB.net is very very similar to VBA.