+ Reply to Thread
Results 1 to 10 of 10

Please help debug this simple macro - created with chat.openAI

  1. #1
    Registered User
    Join Date
    11-07-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    11

    Please help debug this simple macro - created with chat.openAI

    I have managed to create a fairly complex (for me) macro using chat.openAI that searches to find a column then find all partial matching cells then copies those cells plus other cells to a different sheet.

    But this simple one has beat me or beat openAI.

    EDIT - SEE MY LATER POST with attached .xlsm with macro


    The macro:
    Sub FindKeyword()
    Dim KWsearch As String
    KWsearch = ActiveCell.Value

    Dim keywordSheet As Worksheet
    Set keywordSheet = ThisWorkbook.Worksheets("Keyword")
    keywordSheet.Activate

    Dim lastRow As Long
    lastRow = keywordSheet.Cells(keywordSheet.Rows.Count, "A").End(xlUp).Row

    Dim keywordListColumn As Long
    keywordListColumn = 0

    Dim i As Long
    For i = 1 To keywordSheet.Columns.Count
    If keywordSheet.Cells(1, i).Value = "keyword list" Then
    keywordListColumn = i
    Exit For
    End If
    Next i

    For i = 2 To lastRow
    If keywordSheet.Cells(i, keywordListColumn).Value = KWsearch Then
    ' Found a match!
    keywordSheet.Cells(i, keywordListColumn).Activate
    keywordSheet.Parent.VerticalScrollBar.Value = _
    keywordSheet.Cells(i, keywordListColumn).Top - keywordSheet.Parent.Height / 2
    Exit For
    End If
    Next i
    End Sub

    ===========
    My instructions to OpenAI (excluding the scroll part of the macro - mentioned earlier in the chat

    I have a workbook with a sheet named "Keyword"
    The "Keyword" sheet has a column where the title = "Keyword List"
    The macro needs to find that Column and save it's location.

    The user selects a cell in any other sheet. The cell becomes the ActiveCell for that sheet.
    Save the value in that cell as "KWsearch"

    When the user runs the macro it does the following:
    Make "Keyword" shee the ActiveSheet
    Search all cells in the "Keyword List" column
    When a matching cell is found
    Make it the "ActiveCell" in the "Keyword" sheet.

    ===========
    When run the macro makes the "Keyword" sheet the ActiveSheet, but the previously ActiveCell is still active.
    There is no error popup
    ===========

    I have no previous with StepInto, but when I click this and press > play, it does the same as run - makes Keyword sheet active.

    I expected StepInto to highlight and run the code one line at a time but nothing happens in the code window.

    =====
    Any help would be appreciated

    ColinK2
    Last edited by colink2; 12-24-2022 at 08:34 PM.

  2. #2
    Forum Expert
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1,367

    Re: Please help debug this simple macro - created with chat.openAI

    It would be better if you explain what you want. Delete this code and post a sample workbook with some expected results. Should be pretty simple

  3. #3
    Valued Forum Contributor
    Join Date
    11-29-2022
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    341

    Re: Please help debug this simple macro - created with chat.openAI

    If you click Step Into and nothing happens, it sounds like macros have been disabled. Did you check that is not the case?

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Please help debug this simple macro - created with chat.openAI

    The code does work as long as the following are correct.

    The active cell when the code is run contains the keyword to search for.
    the keyword to search for is exactly the same as the text in the keyword list. the comparison is case sensitive.
    the keyword list has a header of 'keyword list' in row 1. Again case sensitive.

    If there is a match then the code will fail trying to set an invalid property "VerticalScrollBar"
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    11-07-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    11

    Re: Please help debug this simple macro - created with chat.openAI

    Thanks
    Macros are enabled . I was using Step Into incorrectly

  6. #6
    Registered User
    Join Date
    11-07-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    11

    Re: Please help debug this simple macro - created with chat.openAI

    Uploading attachment Now sorted - thanks to protonLeah
    Last edited by colink2; 12-24-2022 at 08:35 PM.

  7. #7
    Registered User
    Join Date
    11-07-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    11

    Re: Please help debug this simple macro - created with chat.openAI

    After editing message above thinking upload sorted I cannot upload an attachment and cannot reply to protonLeah.

    Awaiting support.

  8. #8
    Registered User
    Join Date
    11-07-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    11

    Re: Please help debug this simple macro - created with chat.openAI

    This macro in the attached sheet should perform 2 simple tasks with just a few steps:

    User selects any cell in any sheet other than "Keyword" Sheet eg "cat" in Group2 sheet, cell G3
    Run the macro

    The macro should find "cat" in "Keyword List" column of "Keyword" Sheet.
    Make "Keyword" sheet the ActiveSheet and make "Cat" the ActiveCell
    Scroll the "Keyword" sheet until "Cat" is in centre of screen

    After making "Keyword" sheet the active sheet nothing else is done on the sheet (no highlight of "cat" and no scroll.

    Note not concerned about 'case' at this stage

    What I have worked out:

    I have identified that the first loop never ends.

    For i = 1 To keywordSheet.Columns.Count
    If keywordSheet.Cells(1, i).Value = "keyword list" Then
    keywordListColumn = i
    Exit For
    End If
    Next i

    So either the Macro has not saved "cat" as KWsearch OR it is searching in the wrong place and cannot find "cat" in cell D36 of the "keyword" sheet

    Any help would be appreciated.

    Ideally I would like this macro fixed,
    but if unfixable then I would be grateful for a new macro.

    Any help would be appreciated

    ColinK2


    The full macro can be found in the spreadsheet but it is also copied below
    Sub FindKeyword()
    Dim KWsearch As String
    KWsearch = ActiveCell.Value

    Dim keywordSheet As Worksheet
    Set keywordSheet = ThisWorkbook.Worksheets("Keyword")
    keywordSheet.Activate

    Dim lastRow As Long
    lastRow = keywordSheet.Cells(keywordSheet.Rows.Count, "A").End(xlUp).Row

    Dim keywordListColumn As Long
    keywordListColumn = 0

    Dim i As Long
    For i = 1 To keywordSheet.Columns.Count
    If keywordSheet.Cells(1, i).Value = "keyword list" Then
    keywordListColumn = i
    Exit For
    End If
    Next i

    For i = 2 To lastRow
    If keywordSheet.Cells(i, keywordListColumn).Value = KWsearch Then
    ' Found a match!
    keywordSheet.Cells(i, keywordListColumn).Activate
    keywordSheet.Parent.VerticalScrollBar.Value = _
    keywordSheet.Cells(i, keywordListColumn).Top - keywordSheet.Parent.Height / 2
    Exit For
    End If
    Next i
    End Sub

    ===========
    My instructions to OpenAI (excluding the scroll part of the macro - mentioned earlier in the chat

    I have a workbook with a sheet named "Keyword"
    The "Keyword" sheet has a column where the title = "Keyword List"
    The macro needs to find that Column and save it's location.

    The user selects a cell in any other sheet. The cell becomes the ActiveCell for that sheet.
    Save the value in that cell as "KWsearch"

    When the user runs the macro it does the following:
    Make "Keyword" shee the ActiveSheet
    Search all cells in the "Keyword List" column
    When a matching cell is found
    Make it the "ActiveCell" in the "Keyword" sheet.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Please help debug this simple macro - created with chat.openAI

    Please Login or Register  to view this content.
    Last edited by protonLeah; 12-25-2022 at 01:11 AM.
    Ben Van Johnson

  10. #10
    Registered User
    Join Date
    11-07-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    11

    Re: Please help debug this simple macro - created with chat.openAI

    Thanks very much protonLeah - that works perfectly

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Introducing a dynamic heading to created PDF Files created by macro
    By Romoluzzi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2020, 05:42 AM
  2. Pivot Table Macro - Debug Error: Created in 2016 and works, does not work in Excel 2013
    By ashley72788 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-05-2017, 04:07 AM
  3. Macro to review chat log
    By amit6488 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-29-2015, 07:05 AM
  4. Live chat/chat room
    By SChapman in forum Suggestions for Improvement
    Replies: 1
    Last Post: 05-06-2013, 10:05 PM
  5. Read simple random file created with another for of Basic
    By Petrus Vorster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-30-2012, 10:49 AM
  6. [SOLVED] Pivot Chat Macro
    By Rastus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2006, 10:50 AM
  7. [SOLVED] simple (i think) UDF debug help needed
    By Adam Kroger in forum Excel General
    Replies: 4
    Last Post: 12-18-2005, 04:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1