MartinS13X
New Member
- Joined
- Apr 4, 2018
- Messages
- 17
- Office Version
- 2019
- Platform
- Windows
I have a macro which scans lines of text (transaction descriptions) located in Column AAA (703) of sheet "Transactions" for keywords. Those keywords are located in a "Categories array" on another sheet (named "Categories").
When the keywords are found, it outputs them with an offset on the Transactions sheet into column AA.
My question is: How do I change that offset to another sheet, for example Column AA on a sheet called "Output"?
Here is the code for my macro:
Thanks in advance.
When the keywords are found, it outputs them with an offset on the Transactions sheet into column AA.
My question is: How do I change that offset to another sheet, for example Column AA on a sheet called "Output"?
Here is the code for my macro:
Code:
Sub Keyword()
Dim strText As Range
Dim c As Range
Dim r As Range
Dim x As Integer
Dim Text_Col As Long
Dim Cat_sheet As Worksheet 'Categories
Dim Txt_sheet As Worksheet 'Transactions
'--------------------------------------------------------------
Set Txt_sheet = Sheets("Transactions")
Set Cat_sheet = Sheets("Categories")
Text_Col = 703 'Column number in TXT_Sheet with transaction descriptions (AAA=703)
'Offset -667 = AA
'--------------------------------------------------------------
Application.ScreenUpdating = False
With Txt_sheet
Set strText = .Cells(2, Text_Col).Resize(.Cells(Rows.Count, Text_Col).End(xlUp).Row - 1)
For x = 1 To WorksheetFunction.CountA(Cat_sheet.Range("1:1"))
.Cells(1, Text_Col).Offset(, -677 + x) = "Category " & x
For Each c In strText
For Each r In Cat_sheet.Cells(2, x).Resize(Cat_sheet.Cells(Rows.Count, x).End(xlUp).Row - 1)
If InStr(1, UCase(c), UCase(r), 1) > 0 Then
c.Offset(, -677 + x) = c.Offset(, -677 + x) & "" & r
End If
Next r
If Len(c.Offset(, 1 + x)) > 0 Then c.Offset(, 1 + x) = Right(c.Offset(, 1 + x), (Len(c.Offset(, 1 + x)) - 2))
Next c
Next x
.Cells.EntireColumn.AutoFit
End With
With Application
.StatusBar = False
.ScreenUpdating = True
End With
MsgBox "All text has been processed", vbInformation, "Finished"
End Sub
Thanks in advance.
Last edited by a moderator: