VBA code needed

Question

I am very new and writing VBA code and I am having trouble writing a code that will find a partial string value like density, pH, HGR, TSS etc in the A column and then copy that partial string to the next available column. In this case that column is B.  I also need partial strings for elements like Al, Na ect to copy a general term "elements" to the B column.  The italics in Column B is what I would like to see as output based on what is available in column A.

Column A                                          Column B

Al                                                         Element

Na                                                        Element

TSS (Total Suspended Solids)                 TSS

Density                                                 Density

pH                                                        pH

HGR (Hydrogen Generation)                   HGR

Rheology---Viscosity                              Viscosity

Thanks for any help. 

 

Answer 1

Why not using autocorrect feature of office package.

In 2007and higher version office icon at top left->excel option at bottom->proofing

---

Best Regards,
Asadulla Javed, Kolkata
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

Answer 2

Your apparent rules are not simple.  I suspect that once you derive specific rules for what you want to do, coding will be simple.

Option Explicit
Sub DeriveColB()
    Dim v As Variant
    Dim rDest As Range
    Dim i As Long, j As Long
    Dim s As String
    Dim aStart As Variant

'Array of various substrings at beginning, middle or end
aStart = Array("TSS*", "Density*", "pH*", "HGR*", "*Viscosity*")

'get source data
v = Range("A1", Cells(Rows.Count, "A").End(xlUp))

'Destination for results -- first cell
Set rDest = Range("B1").Resize(rowsize:=UBound(v))

'Iterate through data and change to results
'multiple rules apparently
For i = 1 To UBound(v)
    s = v(i, 1)
    If s Like "[A-Z][a-z]" Then
        v(i, 1) = "Element"
    Else
        For j = LBound(aStart) To UBound(aStart)
            If s Like aStart(j) Then
                v(i, 1) = Replace(aStart(j), "*", "")
                Exit For
            End If
        Next j
    End If
Next i

Application.ScreenUpdating = False
rDest.EntireColumn.Clear
rDest = v
Application.ScreenUpdating = True
End Sub

Something that will match the results you show, without knowing exactly what your rules might be

---

Ron

Answer 3

Thanks for the input.  That helped get what I needed.