CODEWITHSUNDEEP
excelvba
Liz H
Liz H

Reputation: 147

Replace text in column, if column header is a specific text

I have a report which plots venue availability against dates (see layout below)

enter image description here

The information comes through as Fullday / Morning / Afternoon, and if used will say Booked.

None of the venues are used on Sundays, except to set up. However, this data still comes through as a full day availability, which messes with calculations used for reporting later down the line (can look like the venues have offered more availability than they actually have).

I have the following two pieces of code in place already: 

Sub Find_replace()
Range("A:AAA").Select
Selection.Replace What:="Fullday", Replacement:="***Full", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
End Sub

This searches for cells with “Fullday” and replaces it with “***Full” across the entire worksheet.

Sub Sunday_widths()
Dim Range1 As Range
Dim Cell1 As Range
Set Range1 = Range("O1:AAA1")
    For Each Cell1 In Range1
        Select Case True
            Case Celll Like "*Sun*"
                Cell1.ColumnWidth = 7.5
        End Select
    Next Cell1
End Sub

This searches for any column with “Sun” in Row 1, and sets the width of the column based on that.

Is there any way to combine these two, so that the macro searches for any column with Sun in the top row, selects that column and replaces any text “***Full” with “Set up”?

Thanks very much in advance!

EDIT:

Sub Sunday_avails()

Dim Range1 As Range
Dim Cell1 As Range
Set Range1 = Range("O1:AAA1")
    For Each Cell1 In Range1
        Select Case True
            Case Celll Like "*Sun*"
                Cell1.EntireColumn.Replace What:="***Full", Replacement:="*Set up", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        End Select
    Next Cell1
End Sub

Upvotes: 1

Views: 86

Answers (2)

JvdV
JvdV

Reputation: 75870

A simplified alternative to stepping over all columns:

Dim cl1 As Long, cl2 As Long, x As Long

With Sheet1 'Specify your sheet's codename
    cl1 = .Range("1:1").Find("Sun").Column
    cl2 = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For x = cl1 To cl2 Step 7
        .Columns(x).Replace What:="***Full", Replacement:="*Set up"
    Next
End With

This would be usefull if you can confirm a step of 7 is appropriate on your data layout. In any case, even if the above isn't applicable, it's usefull to know the last used column to prevent a lot of unneccesary calls to the sheet object =)

Upvotes: 2

BigBen
BigBen

Reputation: 50008

Just call Replace on the .EntireColumn:

Cell1.EntireColumn.Replace What:="***Full", Replacement:="*Set up"...

Add Option Explicit to the top of the module, and it would catch the typo in your latest edit:

Case Celll Like "*Sun*"

should be 

Case Cell1 Like "*Sun*"

Upvotes: 2

Related Questions