Comparing Strings

[danno79]

Hi All,

Been grappling with this for a while - thought I had it solved before christmas but on revisiting, does not produce the reusult i need.

I have the following working code which looks at a range (containing strings of text and mumbers with 3 letters followed by a number eg AAA1, BBB5 etc) and if the fist 3 characters of that string match any of the values in the second range (which would contain text only eg AAA, BBB), to delete that value from the first range i.e;

Dim cell As Range

With ActiveSheet
Dim x As Integer

    For x = 116 To 147
        For Each cell In .Range("M44:M75")
            If Left(cell.Value, 3) = .Range("H" & x).Value Then
                cell.ClearContents
                cell.Interior.ColorIndex = xlNone
            End If
        Next cell
    Next x

End With

I need to alter this so that the code can deal with values of variable length, including spaces, i.e "HELLO THERE1", "GOOD BYE2",etc, and delete those entries if the second range contains "HELLO THERE", "GOOD BYE"

The following was suggested but on further investigation, did not provide the result i was looking for (clears contents of entire range for "M44:M75" , even though there were some values in the first range and not in the second)

Dim cell As Range

With ActiveSheet
Dim x As Integer

    For x = 116 To 147
        
        For Each cell In .Range("M44:M75")
              If Left(cell.Value, len(.Range("H" & x).Value)) = .Range("H" & x).Value Then
                cell.ClearContents
                cell.Interior.ColorIndex = xlNone
            End If
        Next cell
    Next x

End With

It appears that if you replace the "long" number value in the left() expression with anything else, that it will not work as expected - is this the case?

Is there a solution using StrComp or InStr that could be used instead? I'm a relative newbie to vb coding and not that familiar with string manipulation so you will have to bear with me!!

Perhaps it requires a totally different approach altogether - is my use of "For, Next" in the above code o.k?
Any help greatly appreciated.

 

[hiker95]

danno79,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[Kenneth Hobson]

You can remove the preset values if it does what you need. Change the matchcase to True if you need that.

Sub ClearPartialMatches()
  Dim cell As Range, r As Range, hRange As Range, mRange As Range
  
  Range("H116").Value = "HELLO THERE"
  Range("H117").Value = "HELLO THERE1"
  Range("H118").Value = "Hello World!"
  Range("H147").Value = "HELLO THERE Last One"
  Range("M44").Value = "Hello There"
  
  Set hRange = Range("H116:H147")
  Set mRange = Range("M44:M75")
  Set r = hRange
  For Each cell In mRange
    If cell.Value <> Empty Then
      Do
        Set r = hRange.Find(what:=cell.Value, after:=LastCellInRange(hRange), _
           Lookat:=xlPart, searchdirection:=xlNext, MatchCase:=False)
        If r Is Nothing Then Exit Do
        r.ClearContents
        r.Interior.ColorIndex = xlNone
      Loop Until r Is Nothing
    End If
  Next cell
End Sub

Function LastCellInRange(r As Range) As Range
  Set LastCellInRange = r.Cells(r.Rows.Count, r.Columns.Count)
End Function

 

[danno79]

thanks for your reply hiker,

Ok so here is what data looks like before;

Excel Workbook
	K	L	M
43		MON - WED Group Allocation	WED- FRI Group Allocation
44	1	DDDDDDDDDD1	XXXXXXXXXXXXX4
45	2	DDDDDDDDDD2	XXXXXXXXXXXXX5
46	3	DDDDDDDDDD3	XXXXXXXXXXXXX6
47	4	DDDDDDDDDD4
48	5	DDDDDDDDDD5
49	6	XXXXXXXXXXXXX1
50	7	XXXXXXXXXXXXX2
51	8	XXXXXXXXXXXXX3
52	9	XXXXXXXXXXXXX4	DDDDDDDDDD1
53	10	XXXXXXXXXXXXX5	DDDDDDDDDD2
54	11	XXXXXXXXXXXXX6	DDDDDDDDDD3
55	12		DDDDDDDDDD4
56	13		DDDDDDDDDD5
57	14		XXXXXXXXXXXXX1
58	15		XXXXXXXXXXXXX2
59	16		XXXXXXXXXXXXX3
60	1
61	2
62	3
63	4
64	5
65	6
66	7
67	8
68	9
69	10
70	11
71	12
72	13
73	14
74	15
75	16
MAIN DATA ENTRY2

the range "h116:h147" being compared with "m44:m75"

Excel Workbook
	B	C	D	E	F	G	H	I	J
115	Organization Name	Booking period	No of Nights	No of acts	Act Code	Start Day	Abreviation / Background colour	No of Groups	No Of clients
116	xxxxxxxxxxxxx	Mon-Wed	2	7	2	1	XXXXXXXXXXXXX	6	60
117
118
MAIN DATA ENTRY2

and what it should look like after comparison;

Excel Workbook
	K	L	M
43		MON - WED Group Allocation	WED- FRI Group Allocation
44	1	DDDDDDDDDD1
45	2	DDDDDDDDDD2
46	3	DDDDDDDDDD3
47	4	DDDDDDDDDD4
48	5	DDDDDDDDDD5
49	6	XXXXXXXXXXXXX1
50	7	XXXXXXXXXXXXX2
51	8	XXXXXXXXXXXXX3
52	9	XXXXXXXXXXXXX4	DDDDDDDDDD1
53	10	XXXXXXXXXXXXX5	DDDDDDDDDD2
54	11	XXXXXXXXXXXXX6	DDDDDDDDDD3
55	12		DDDDDDDDDD4
56	13		DDDDDDDDDD5
57	14
58	15
59	16
60	1
61	2
62	3
63	4
64	5
65	6
66	7
67	8
68	9
69	10
70	11
71	12
72	13
73	14
74	15
75	16
MAIN DATA ENTRY2

hope this is clearer

 

[danno79]

You can remove the preset values if it does what you need. Change the matchcase to True if you need that.

Sub ClearPartialMatches()
  Dim cell As Range, r As Range, hRange As Range, mRange As Range
  
  Range("H116").Value = "HELLO THERE"
  Range("H117").Value = "HELLO THERE1"
  Range("H118").Value = "Hello World!"
  Range("H147").Value = "HELLO THERE Last One"
  Range("M44").Value = "Hello There"
  
  Set hRange = Range("H116:H147")
  Set mRange = Range("M44:M75")
  Set r = hRange
  For Each cell In mRange
    If cell.Value <> Empty Then
      Do
        Set r = hRange.Find(what:=cell.Value, after:=LastCellInRange(hRange), _
           Lookat:=xlPart, searchdirection:=xlNext, MatchCase:=False)
        If r Is Nothing Then Exit Do
        r.ClearContents
        r.Interior.ColorIndex = xlNone
      Loop Until r Is Nothing
    End If
  Next cell
End Sub

Function LastCellInRange(r As Range) As Range
  Set LastCellInRange = r.Cells(r.Rows.Count, r.Columns.Count)
End Function

Kenneth - I changed the prests in your code to that below;- but, it did not clear "HELLO THERE1" and "HELLO THERE2" and leave "goodbye then1" that is what i am trying to achieve - any suggestions pls?

  Range("m44").Value = "HELLO THERE1"
  Range("m45").Value = "HELLO THERE2"
  Range("m46").Value = "goodbye then1"
  Range("h116").Value = "HELLO THERE"

 

[Kenneth Hobson]

You went just opposite to what I thought you meant. In that case, just reverse the two set ranges to:

Set mRange = Range("H116:H147")
  Set hRange = Range("M44:M75")

 

[danno79]

You went just opposite to what I thought you meant. In that case, just reverse the two set ranges to:

Set mRange = Range("H116:H147")
  Set hRange = Range("M44:M75")

That works exactly as I need - thank you! This forum is has been so helpful (you guys are coding geniuses!) I just wish I had enough knowledge / expertise to be able to offer similar help to others.....