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
Bookmarks