Help with macro output to other sheet

MartinS13X

New Member
Joined
Apr 4, 2018
Messages
17
Office Version
  1. 2019
Platform
  1. 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:

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:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,215,045
Messages
6,122,830
Members
449,096
Latest member
Erald

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top