Hello:
I have 2 different macros that I would like to combine into one macro depending on the text in one of the columns.
Dataset has several columns/rows and column C (named "TYPE"), can have different values, all are strings. If cell equals "TYPE_A", then I want the 1st macro to work and if it equals "TYPE_B" I want the 2nd macro to work. If it contains anything else, I don't want anything to be done.
Here are the 2 macros I'm currently working with:
Thanks
I have 2 different macros that I would like to combine into one macro depending on the text in one of the columns.
Dataset has several columns/rows and column C (named "TYPE"), can have different values, all are strings. If cell equals "TYPE_A", then I want the 1st macro to work and if it equals "TYPE_B" I want the 2nd macro to work. If it contains anything else, I don't want anything to be done.
Here are the 2 macros I'm currently working with:
VBA Code:
Sub Format_Inflation()
Dim SelRange As Range
Dim ColNum As Integer
Dim CWS As Worksheet, TmpWS As Worksheet
Set CWS = ActiveSheet
ColNum = Application.WorksheetFunction.Match("DEAL_MARKET_DATA", CWS.Rows(1), 0)
Set SelRange = CWS.Columns(ColNum)
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Dim lr As Long
Dim r As Long
Dim arr() As String
Dim i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, (ColNum)).End(xlUp).Row
If lr < 2 Then Exit Sub
For r = 2 To lr
If Cells(r, (ColNum)) <> "" Then
arr = Split(Cells(r, (ColNum)), ";")
'If Not IsEmpty(arr) Then
i = UBound(arr)
'End If
If i > 0 Then
Cells(r, (ColNum)) = arr(i - 13)
End If
End If
Next r
Application.ScreenUpdating = True
End Sub
VBA Code:
Sub Format_Mtg()
Dim SelRange As Range
Dim ColNum As Integer
Dim CWS As Worksheet, TmpWS As Worksheet
Set CWS = ActiveSheet
ColNum = Application.WorksheetFunction.Match("DEAL_MARKET_DATA", CWS.Rows(1), 0)
Set SelRange = CWS.Columns(ColNum)
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Dim lr As Long
Dim r As Long
Dim arr() As String
Dim i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, (ColNum)).End(xlUp).Row
If lr < 2 Then Exit Sub
For r = 2 To lr
If Cells(r, (ColNum)) <> "" Then
arr = Split(Cells(r, (ColNum)), ";")
'If Not IsEmpty(arr) Then
i = UBound(arr)
'End If
If i > 0 Then
Cells(r, (ColNum)) = arr(i - 5)
End If
End If
Next r
Application.ScreenUpdating = True
End Sub
Thanks