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

Coder119

New Member
Joined
Oct 14, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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?

1665746800157.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi, welcome to the forum!

How about:

VBA Code:
Sub Cheese()
With Intersect(Columns(Application.Match("Cheese", Rows(1), 0)), ActiveSheet.UsedRange).Offset(1)
    .Value = Evaluate("LEFT(" & .Address & ",4)")
End With
End Sub
 
Upvote 0
Welcome to the Board!

Try this code:
VBA 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
 
Upvote 0
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.
 
Upvote 0
Thank you both I will give these a try and let you know if all works well. Thanks again!
 
Upvote 0
I received an error on the. .Address stating invalid or unqualified reference.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,551
Messages
6,179,480
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top