PDA

View Full Version : [SOLVED] VBA to group and identify



jpitcairn
10-18-2018, 12:42 PM
Hello. I've been going through a database export of our part codes at work and I am a VBA novice. I'm trying to isolate wrong part numbers and the correct part numbers start with letters. In Column D, I want to be able to identify the wrong ones with "Invalid Part Number" in a yellow highlighted cell, then isolate the valid part numbers as "In-House", for our own proprietary parts, and "Outsource" for those that we purchase. Our in-house part numbers, in column C, start with even numbers and those we outsource are odd numbered. TIA for any help I can receive. I am a novice who is teaching himself VBA but this is way above my head.


23063

Paul_Hossler
10-18-2018, 01:08 PM
Simple approach (if I understood the requirements correctly)




Option Explicit


Sub PartNumbers()
Dim rData As Range
Dim i As Long
Dim sPN As String, sPN1 As String, sPN2 As String

Application.ScreenUpdating = False

Set rData = ActiveSheet.Cells(1, 1).CurrentRegion ' A1:D23

For i = 2 To rData.Rows.Count ' rows 2 - 23
sPN = rData.Cells(i, 1).Value ' C3972
sPN1 = Left(sPN, 1) ' C
sPN2 = Mid(sPN, 2, 1) ' 3
Select Case sPN1
Case "0" To "9"
rData.Cells(i, 4).Value = "Invalid Part Number"
rData.Cells(i, 4).Interior.Color = vbYellow

Case Else

Select Case sPN2
Case "0", "2", "4", "6", "8"
rData.Cells(i, 4).Value = "In-House"
Case "1", "3", "5", "7", "9"
rData.Cells(i, 4).Value = "Outsource"
End Select
End Select
Next i
Application.ScreenUpdating = True

MsgBox "Done"

End Sub

jpitcairn
10-18-2018, 06:14 PM
It worked!!! Thank you!!