如何在数据中找到部分字符串?

So I have been working on creating a macro to parse large data sets and I am on the cusp of completion. I am attempting to analyze dates by month and ignore the year so that I can qualify various if statements to print a string in another column. Printing the string and everything else is not a problem it's just finding the date. I don't care so much about the year more just the "Jan" "Feb" "Mar" so that I can determine if it's Q1, Q2, Q3 1H, 2H etc, etc. The code that I have that I'm attempting to do this with is in the legCounter = 3 block below. (I put two asterisk next to the line that isn't doing what it's supposed to).

因此，我一直在创建一个宏来解析大型数据集，现在我正处于完成阶段。我尝试逐月分析日期，忽略年份，以便我可以限定各种if语句在另一列中打印字符串。打印字符串和其他东西不是问题，它只是找到日期。我不太关心一年，我只关心1月2月3日，3月3日等等，这样我就可以确定这是Q1, Q2, Q3 1H, 2H等等。(我把两个星号放在不做它应该做的事情的线旁边)。

'Counts legs to narrow structure type
Do
DoEvents
    row_color_tester = row_number

    If tradeTime = Range("B" & (row_color_tester + 1)).Text Or tradeTime = Range("B" & (row_color_tester - 1)).Text Then
        legCounter = legCounter + 1

    End If

    row_number = row_number + 1

Loop Until tradeTime <> Range("B" & (row_color_tester + 1)).Text

'Analyzes individual legs to determine specific structure type
    If legCounter = 2 Then
        Range("Q" & (row_number - legCounter)) = "Spread"
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4
    **ElseIf legCounter = 3 Then
        If StrComp(Range("H" & (row_number - 1)).Text, "Jan", vbTextCompare) > 0 & StrComp(Range("H" & (row_number - 2)).Text, "Feb", vbTextCompare) > 0 & StrComp(Range("H" & (row_number - 3)).Text, "Mar", vbTextCompare) > 0 Then**
            Range("Q" & (row_number - legCounter)) = "Q1"
            Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4
        End If
    ElseIf legCounter = 4 Then
        Range("Q" & (row_number - legCounter)) = legCounter
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4
    ElseIf legCounter = 5 Then
        Range("Q" & (row_number - legCounter)) = legCounter
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4
    End If

  Loop Until row_number = lastRow

The data is stored in Column H and is in this format :

数据存储在H列，格式如下:

16-Mar
16-Feb
16-Jan

2 个解决方案

#1

It looks to me like you want either the InStr function or just the Right function to determine the month part of the Range.Text property. The StrCmp function compares the whole string and returns a sorting position.

在我看来，你要么想要InStr函数，要么只想要一个正确的函数来确定范围的月份部分。文本属性。StrCmp函数比较整个字符串并返回排序位置。

ElseIf legCounter = 3 Then
    If Instr(1, Range("H" & (row_number - 1)).Text, "Jan", vbTextCompare) > 0 Or _
       Right(Range("H" & (row_number - 2)).Text, 3) = "Feb" Or _
       Range("H" & (row_number - 3)).Text Like "*Mar" Then
        Range("Q" & (row_number - legCounter)) = "Q1"
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4
    End If
...

Those are three text comparison methods. For all four of the quarters, a Select Case statement would likely be more appropriate.

这是三种文本比较方法。对于这四个季度，一个选择案例的陈述可能更合适。

ElseIf legCounter = 3 Then
    Select Case Right(Range("H" & (row_number - 2)).Text, 3)
      Case "Jan", "Feb", "Mar"
        Range("Q" & (row_number - legCounter)) = "Q1"
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4
      Case "Apr", "May", "Jun"
        Range("Q" & (row_number - legCounter)) = "Q2"
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4 '?
      Case "Jul", "Aug", "Sep"
        Range("Q" & (row_number - legCounter)) = "Q3"
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4 '?
      Case "Oct", "Nov", "Dec"
        Range("Q" & (row_number - legCounter)) = "Q4"
        Rows(row_number - legCounter).EntireRow.Interior.ColorIndex = 4 '?
    End Select
...

#2

This small bit will take your range and return the Quarter number.

这一小部分将取你的范围并返回四分之一。

Format(CDate(Range("H" & (row_number - 1)).Text),"q")

格式(CDate(范围(H &(row_number - 1))。text),“问”)

#1