VBA conditional searching for entire cell contents

Time2Learn

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I am trying to write a VBA module to help me identify the source of an excel file that I am given based on known cell values in the header. For example if ""Service Waiver Acknowledged"" exists in any position of the range A1:Z5 then I know this file came from Software1 whereas if ""AnnivDay"" exists in any position of the range A1:Z5 then I know this file came from Software2. I know this because those are unique cell values that do not exist from any other software source in my sample. I was originally doing this:


VBA Code:
Range("A1").Select
ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[1]=""Last name"",RC[2]=""First name"",RC[12]=""Last Seen"",RC[14]=""""),""Software1 List"","""")"
    Range("A2").Select

the problem with this is someone may have done formatting and moved these columns around or removed one, which would make this fail and not show any result.
Next I tried something like this:

VBA Code:
Dim rng As Range

Set rng = ActiveSheet.Range("A1:Z5")

For Each cell In rng.Cells
   
    If InStr(1, cell, "Last Seen") > 0 Then
        MsgBox ("Software1")
   ElseIf InStr(1, cell, "Last Seen Date") > 0 Then
        MsgBox ("Software2")

The issue here is that Software2 uses ""Last Seen Date"" so this code will not accurately identify ""Last Seen Date"" as Software2.

I've been having trouble finding a good solution for this as I am relatively new to VBA and my googling hasn't provided a good solution.

This is what I'm looking for and I'm sure I'm just not using correct phrasing
If an entire cell contents ""LastSeen"" appears in A1:Z5, then MsgBox ("Software1")
then I can continue that logic with the ElseIf statements for my list of unique identifiers.

Thank you for any suggestions, including how to better post here as this is my first time posting, long time lurker.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,976
Office Version
  1. 2010
Platform
  1. Windows
You could try changing th logic like this:
VBA Code:
Dim rng As Range

Set rng = ActiveSheet.Range("A1:Z5")

For Each cell In rng.Cells
   
    If InStr(1, cell, "Last Seen") > 0 And InStr(1, cell, "Last Seen Date") = 0 Then
        MsgBox ("Software1")
   ElseIf InStr(1, cell, "Last Seen Date") > 0 Then
        MsgBox ("Software2")
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
A demonstration as a beginner starter :​
VBA Code:
Sub Demo1()
        V = [{"Last name","First name","Last Seen","Software1 List"}]
    With Application
        For R& = 1 To 5
            W = Filter(.IfError(.Match([A:Z].Rows(R), V, 0), False), False, False)
            If UBound(W) > -1 Then MsgBox "Software " & W(0): Exit For
        Next
    End With
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
If you want to test cell by cell - instead of row by row like my demonstration - so obviously check 'Last Seen Date' before 'Last Seen' ‼​
The array variable V in my demonstration is for an exact match so 'Last Seen' can't be confused with 'Last Seen Date', just mod this variable …​
 

Time2Learn

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You could try changing th logic like this:
VBA Code:
Dim rng As Range

Set rng = ActiveSheet.Range("A1:Z5")

For Each cell In rng.Cells
  
    If InStr(1, cell, "Last Seen") > 0 And InStr(1, cell, "Last Seen Date") = 0 Then
        MsgBox ("Software1")
   ElseIf InStr(1, cell, "Last Seen Date") > 0 Then
        MsgBox ("Software2")
Thank you, this is working well for me. I appreciate the help
 

Time2Learn

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
A demonstration as a beginner starter :​
VBA Code:
Sub Demo1()
        V = [{"Last name","First name","Last Seen","Software1 List"}]
    With Application
        For R& = 1 To 5
            W = Filter(.IfError(.Match([A:Z].Rows(R), V, 0), False), False, False)
            If UBound(W) > -1 Then MsgBox "Software " & W(0): Exit For
        Next
    End With
End Sub
Thank you for taking time to respond. Part of this is over my head at the moment but gives me additional items to look up and learn about, so thank you for this.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

(removed as can't work like this)
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
A partial match demonstration where a cell which contains 'Last Seen Date' is found like as 'Last Seen' :​
VBA Code:
Sub Demo2()
        V = [{"Last name","First name","Last Seen","Software1 List"}]
    For N% = 1 To UBound(V)
        If Not [A1:Z5].Find(V(N), , xlValues, xlPart) Is Nothing Then MsgBox "Software " & N: Exit For
    Next
End Sub
For an exact match replace xlPart by xlWhole and add 'Last Seen Date' in the array variable …​
 
Last edited:

Time2Learn

New Member
Joined
Apr 22, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
A partial match demonstration where a cell which contains 'Last Seen Date' is found like as 'Last Seen' :​
VBA Code:
Sub Demo2()
        V = [{"Last name","First name","Last Seen","Software1 List"}]
    For N% = 1 To UBound(V)
        If Not [A1:Z5].Find(V(N), , xlValues, xlPart) Is Nothing Then MsgBox "Software " & N: Exit For
    Next
End Sub
For an exact match replace xlPart by xlWhole and add 'Last Seen Date' in the array variable …​
In this case, would I be able to declare multiple arrays? I mean I have about 50 softwares, each with their own unique identifiers so would I be able to list arrays for each of those and if a xlWhoe match is detected it would identify that software name such as Software2 or Software 30 for example? Would I just declare those like v = [{"Last Name", "First Name","Last Seen","Software1 List"}] and W = [{"Last Seen Date","Cell Phone Number","Software3 List"}] or somthing along those lines?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
Why not using a 'Settings' worksheet rather than hardcode arrays ?​
 

Forum statistics

Threads
1,141,284
Messages
5,705,502
Members
421,399
Latest member
hjweiss00

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
Top