VBA Code find specific column header& keep first 4 characters in each cell.

[Coder119]

I need to write a code that looks for a specific column header and then keeps only the first 4 characters in each cell in that column. I have looked at differed pieces of code and nothing works. I don't want to enter Left function manually into to each cell on the sheet because I have multiple formatting that I need to do, and I want to have that in a VBA format. So far, I am writing individual code for each format and then calling them together into one macro. However, I am stuck on this one. Any suggestions? Below, I have just included a dummy example as my data is not sharable. Using the example below let's say I want the column "Cheese" to only show the first 4 characters in each cell, but that column may not always fall in column D, so the position of that header could change from time to time. What VBA code could I use to accomplish this?

 

[FormR]

Hi, welcome to the forum!

How about:

Sub Cheese()
With Intersect(Columns(Application.Match("Cheese", Rows(1), 0)), ActiveSheet.UsedRange).Offset(1)
    .Value = Evaluate("LEFT(" & .Address & ",4)")
End With
End Sub

 

[Joe4]

Welcome to the Board!

Try this code:

Sub MyMacro()
    
    Dim str As String
    
'   Enter string to look for
    str = "Cheese"
    
'   Find value in row 1
    On Error GoTo err_chk
    Rows("1:1").Find(What:=str, After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

'   Use "Text to Columns" to only keep first 4 characters of column
    Columns(ActiveCell.Column).TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(4, 9)), TrailingMinusNumbers:=True

'   Fix Header
    ActiveCell = str
    
    Exit Sub
    
err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find value of " & str & " in row 1", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[Joe4]

FormR's code is a lot more concise than mine.
However, you may want to add error-handling, like I have in mine, so it can gracefully handle the error if "Cheese" is not found in row 1.

 

[Coder119]

Thank you both I will give these a try and let you know if all works well. Thanks again!

 

[Coder119]

I received an error on the. .Address stating invalid or unqualified reference.

 

[Joe4]

I received an error on the. .Address stating invalid or unqualified reference.

Did you try my code to see if it works?

 

[Coder119]

I need it to look at the column that has the matched header and make the changes to every cell in the column that would start would row 2.

 

[Joe4]

I need it to look at the column that has the matched header and make the changes to every cell in the column that would start would row 2.

Are you saying that your data starts in row 2, or the header starts in row 2?
If the header is in row 1 and the data starts in row 2, then my code should work.

 

[Coder119]

Data will always start in row 2. A few weeks ago when I started this I found code online from stack overflow as I needed to concatenate some columns and so I started out thinking maybe I could use the meat of that code for this too just changing some things so it would use the left function to leave only first 4 characters. However, I could not get that to work. So I came here hoping to find some fresh code that would leave only the first 4 characters. I was unsure I was if I was allowed to post the code I found there which I used concatenate a few things, It does the matching of the headers, it inserts a column and renames it, but at this point, I need to use the left function and not the concatenate so I set that aside.