VBA If cells contains part of a text among other characters

[SandroUK]

Hi Guys,

Double question here, first if you have any idea what I am doing wrong, the other one if you think for my particular job challenge I am better off learning more Power Query and Power Pivot.

So, I have 10 tables, each tables is a market segment, each market segments has three columns as below:

DAY OF THE WEEK / UNIT SOLD / AVERAGE PER UNIT


The column showing the date is formatted as below:

Mon 1 Apr 19

Tue 2 Apr 19

Wed 3 Apr 19

Thu 4 Apr 19

Fri 5 Apr 19

I am trying to write a code that based on user selecting a segment let's say MEETINGS or PARTIES and a given day, i.e. MONDAY or FRIDAY will check the average by going into the relative table and looking for all "Mon" and "Fri" on the first column and providing me an average. The tables are the same for each month, so 12 Tabs with 10 tables in each tab.

Now I have just started and I came across this hurdle which I did not cover in any of the courses I have done, not in a simple way at least.

Sub HighLight()

Dim x As Long
Dim lastrow As Long

lastrow = Range("A" & Rows.Count).End(xlUp).Row

    For x = 1 To lastrow
        If Range("A" & x).Value = "*Mon*" Then
            Range("A" & x, "D" & x).Select
         Exit Sub
        End If
    Next x
        

End Sub

I used select just to test if it does take my "Mon" into account, it does not, I tried with "?" as well but nothing, of course if I write "Mon 1 Apr 19" it works, I am aware of "Len" "Search" however I would assume there is an easier way to do so instead of writing more than it would take to go do it manually?

This the current challenge.

Not sure if learning something like Power Query or Power Pivot would actually be easier and faster than writing VBA code really...I was watching a couple of videos today, perhaps with that I can make relationship between all "Data Column", I would rather keep my date as it is for perhaps further needs instead of trimming it and leave just the MON,TUE,WED etc... per table.

 

[NdNoviceHlp]

Hi SandroUK. Maybe this will get U started. Please save a copy of your wb before trialing the code and note the code comments re. return to normal. HTH. Dave

Sub HighLight()
Dim x As Long
Dim lastrow As Long, sht As Worksheet
For Each sht In ThisWorkbook.Sheets
lastrow = sht.Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To lastrow
If InStr(LCase(CStr(sht.Range("A" & x).Value)), LCase("Mon")) Then
sht.Range("A" & x, "D" & x).Interior.Color = vbCyan 'blue
'***To return to normal, comment out above line and remove comment below
'sht.Range("A" & x, "D" & x).Interior.Color = vbWhite 'white
sht.Range("A" & x, "D" & x).Borders.LineStyle = xlContinuous
sht.Range("A" & x, "D" & x).Borders.Color = RGB(170, 170, 170) 'grey
End If
Next x
Next sht
End Sub

 

[DanteAmor]

To test try:

If Range("A" & x).Value like "*Mon*" Then