CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

Exract a Substring from a String within a Range

I want to search a range for a string, then extract a substring from the string. The format of the string is xxx-xxx-xxx-xxx, where the x's may be letters or numbers. I am trying to identify the substring in reference to the "-". This code returns the "type mismatch" error on the line before End If.

For m = 7 To 16
    If InStr(EIRPBudget.Cells(m, 12), "-") Then
        FilterDescrip = EIRPBudget.Cells(m, 12)
        EIRPSummary.Range("V" & i + 5) = Split(FilterDescrip, "-")(0)
    End If
Next

Any assistance would be appreciated.

Upvotes: 1

Views: 847

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

The Type Mismatch is caused by this part: EIRPSummary.Range("V" & i = 5) which is evaluated as "VFALSE" if i isn't 5 and "VTRUE" if it is...

UPDATE

I don't think you can get away with all-VBA without a loop, since you need to be iterating individual cells in a range. However you can modify your code to only evaluate the cell value once per iteration... but I don't think there's going to be a huge impact over the small number of iterations you're doing:

Dim cellValue As String
For m = 7 To 16
    cellValue = EIRPBudget.Cells(m, 12)
    If InStr(cellValue, "-") Then _
        EIRPSummary.Range("V" & i + 5) = Split(cellValue, "-")(0)
Next

Alternatively, if you can add another column in your worksheet, =LEFT([A1],FIND("-",[A1])-1) (where [A1] is the cell with "xxx-xxx-xxx-xxx") gives you just that: the n left-most characters up to the position of the first occurrence of "-", excluding that character.

Upvotes: 3

Related Questions