Comparing Strings

danno79

New Member
Joined
Oct 28, 2009
Messages
45
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;

Code:
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)

Code:
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
You can remove the preset values if it does what you need. Change the matchcase to True if you need that.

Code:
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
 
Last edited:
Upvote 0
thanks for your reply hiker,

Ok so here is what data looks like before;

Excel Workbook
KLM
43MON - WED Group AllocationWED- FRI Group Allocation
441DDDDDDDDDD1XXXXXXXXXXXXX4
452DDDDDDDDDD2XXXXXXXXXXXXX5
463DDDDDDDDDD3XXXXXXXXXXXXX6
474DDDDDDDDDD4
485DDDDDDDDDD5
496XXXXXXXXXXXXX1
507XXXXXXXXXXXXX2
518XXXXXXXXXXXXX3
529XXXXXXXXXXXXX4DDDDDDDDDD1
5310XXXXXXXXXXXXX5DDDDDDDDDD2
5411XXXXXXXXXXXXX6DDDDDDDDDD3
5512DDDDDDDDDD4
5613DDDDDDDDDD5
5714XXXXXXXXXXXXX1
5815XXXXXXXXXXXXX2
5916XXXXXXXXXXXXX3
601
612
623
634
645
656
667
678
689
6910
7011
7112
7213
7314
7415
7516
MAIN DATA ENTRY2




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

Excel Workbook
BCDEFGHIJ
115Organization NameBooking periodNo of NightsNo of actsAct CodeStart DayAbreviation / Background colourNo of GroupsNo Of clients
116xxxxxxxxxxxxxMon-Wed2721XXXXXXXXXXXXX660
117
118
MAIN DATA ENTRY2


and what it should look like after comparison;

Excel Workbook
KLM
43MON - WED Group AllocationWED- FRI Group Allocation
441DDDDDDDDDD1
452DDDDDDDDDD2
463DDDDDDDDDD3
474DDDDDDDDDD4
485DDDDDDDDDD5
496XXXXXXXXXXXXX1
507XXXXXXXXXXXXX2
518XXXXXXXXXXXXX3
529XXXXXXXXXXXXX4DDDDDDDDDD1
5310XXXXXXXXXXXXX5DDDDDDDDDD2
5411XXXXXXXXXXXXX6DDDDDDDDDD3
5512DDDDDDDDDD4
5613DDDDDDDDDD5
5714
5815
5916
601
612
623
634
645
656
667
678
689
6910
7011
7112
7213
7314
7415
7516
MAIN DATA ENTRY2


hope this is clearer
 
Upvote 0
You can remove the preset values if it does what you need. Change the matchcase to True if you need that.

Code:
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?

Code:
  Range("m44").Value = "HELLO THERE1"
  Range("m45").Value = "HELLO THERE2"
  Range("m46").Value = "goodbye then1"
  Range("h116").Value = "HELLO THERE"
 
Upvote 0
You went just opposite to what I thought you meant. In that case, just reverse the two set ranges to:
Code:
Set mRange = Range("H116:H147")
  Set hRange = Range("M44:M75")
 
Upvote 0
You went just opposite to what I thought you meant. In that case, just reverse the two set ranges to:
Code:
Set mRange = Range("H116:H147")
  Set hRange = Range("M44:M75")

That works exactly as I need - thank you! :biggrin: 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.....
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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