Time2Learn
New Member
- Joined
- Apr 22, 2021
- Messages
- 11
- Office Version
- 365
- Platform
- 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:
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:
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.
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.