Excel macro to if contains else

Question

I have a excel macro to transform columns from one sheet to the new sheet.

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets.Add.Name = "Sheet2"
Worksheets("Sheet2").Cells(1, 1).Value = "Column A"
Worksheets("Sheet2").Cells(1, 2).Value = "Column B"
Worksheets("Sheet2").Cells(1, 3).Value = "Column C"
Worksheets("Sheet2").Cells(1, 4).Value = "Column D"

Worksheets("data").Activate
SourceColumn = 2
SourceRow = 2
Cells(SourceRow, 1).Activate
TargetRow = SourceRow
targetcolumn = 1
batchValue = InputBox("Enter value for Batch ID column")

While Cells(1, SourceColumn).Value <> ""

While ActiveCell.Value <> ""
Worksheets("Sheet2").Cells(TargetRow, 1).Value = batchValue
Worksheets("Sheet2").Cells(TargetRow, targetcolumn + 1).Value = ActiveCell.Value
Worksheets("Sheet2").Cells(TargetRow, targetcolumn + 2).Value = Worksheets("Data").Cells(1, SourceColumn).Value
Worksheets("Sheet2").Cells(TargetRow, targetcolumn + 3).Value = Worksheets("Data").Cells(SourceRow, SourceColumn).Value
SourceRow = SourceRow + 1
targetcolumn = 1
TargetRow = TargetRow + 1
Cells(SourceRow, 1).Activate
Wend
SourceColumn = SourceColumn + 1
SourceRow = 2
Cells(SourceRow, 1).Activate

Wend
With Worksheets("Sheet2").Sort
.SetRange Range(Cells(2, 1), Cells(TargetRow, 3))
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With


End Sub

This works fine but I need to trim parenthesis from "Column C" In the While statement, I could successfully trim parenthesis by using mid function by updating the code like below.

Worksheets("Sheet2").Cells(TargetRow, targetcolumn + 2).Value = mid(Worksheets("Data").Cells(1, SourceColumn).Value, 2, 36)

But what I found out was that there are some values that has different length which requires....

mid(Worksheets("Data").Cells(1, SourceColumn).Value, 2, 10)

So I need to insert..

if Column C contains the word "parent" then mid ([Column C], 2,10) else mid([Column C],2,36)

What will be the easiest way to complete this job?

Thank you!

bobajob bobajob · Accepted Answer · 2016-11-16T16:47:45

If you just need to trim off the first and last character, then you can use the length of the string in the function parameters:

Worksheets("Sheet2").Cells(TargetRow, targetcolumn + 2).Value = mid(Worksheets("Data").Cells(1, SourceColumn).Value, 2, Len(Worksheets("Data").Cells(1, SourceColumn).Value) - 2)

which will work for any length string.

Excel macro to if contains else

1 Answers