VBA to Search and Extract 7 Values from a Single Cell

[jay_hl]

Hello

I download a report in Excel, where the following value which explains the report filters, is placed in a single cell's value. This cell does move around a little on the report.

Firstly I need to search the report range A1:K10 to find this cell that contains the filters, by looking up either "Item:", or "Location Filter:", or "Period:" or "Sale:". Then secondly use that cell's value to split (and trim) this into ~7 seperate variables to use elsewhere in the VBA code.

e.g A4.Value = "Item: Location Filter: Store 1, Store 2, Store 3, Period: 13.08.2024..30.08.2024 Sale: 1234"

~7 variables then
ReportItem = ""
ReportLocation1 = "Store 1"
ReportLocation2 = "Store 2"
ReportLocation3 = "Store 3"
ReportStartDate = "13/08/2024
ReportEndDate = "30/08/2024
ReportSale = "1234"

The list of stores is usually 1 or 2, but can be 5+. Is there any clever way to handle this by looking at the cell's value?

Can someone please help me as I am slowly getting myself into a hole on this one!?

Thanks

Jay

 

[Jeffrey Mahoney]

I tested this and it worked for me. Is this what you were looking for? The variables are stored as public so you can use them in other code

Public RptItem As Variant
Public RptLoc1 As Variant
Public RptLoc2 As Variant
Public RptLoc3 As Variant
Public RptDateBeg As Date
Public RptDateEnd As Date
Public RptSale As Variant



Sub SplitFilterVals()
  Dim i As Long
  Dim L As Long
  Dim s As Long
  Dim d1 As Long
  Dim d2 As Long
  Dim p As Long
  Dim aStr As String
  Dim Ary As Variant
  Dim Cel As Range
  Dim a As String
  
  For Each Cel In Range("A1:K10")
    If Left(Cel.Value, 5) = "Item:" Then
      aStr = Cel.Value
      Exit For
    End If
  Next Cel
  
  
    'Get beginning of each section
  i = InStr(aStr, "Item:")
  L = InStr(aStr, "Location Filter:")
  p = InStr(aStr, "Period:")
  d1 = p + 8
  d2 = InStr(d1, aStr, "..")
  s = InStr(aStr, "Sale:")
  
  
    'Get variables and store
  RptItem = Mid(aStr, i + 6, L - (i + 6))
    'Locations
  a = Mid(aStr, L + 17, p - (L + 17))
  Ary = Split(a, ",")
  RptLoc1 = Trim(Ary(0))
  RptLoc2 = Trim(Ary(1))
  RptLoc3 = Trim(Ary(2))
    'Dates
  a = Mid(aStr, d1, d2 - d1)
  RptDateBeg = DateSerial(Right(a, 4), Mid(a, 4, 2), Left(a, 2))
  a = Mid(aStr, d2 + 2, s - (d2 + 2))
  RptDateEnd = DateSerial(Right(a, 4), Mid(a, 4, 2), Left(a, 2))
    'Sale
  RptSale = Mid(aStr, s + 6, 100)

  Debug.Print RptItem
  Debug.Print RptLoc1 & ", " & RptLoc2 & ", " & RptLoc3
  Debug.Print RptDateBeg & ", " & RptDateEnd
  Debug.Print RptSale
  
End Sub