Forum Discussion
Trying to Automate a query
Hi everyone.
I have some data that I grab each day from an SQL query and then paste it into a master spreadsheet.
Column D is a series of text that I read and then select an appropriate option from a picklist in column E.
My question is - can I automate this?
I would like a function whereby keywords are searched for in column D and if located, the appropriate option is selected from the picklist in column E.
For example:
'I don't know much about excel. I'm new to it' in column D
'new' would be the keyword and then the spreadsheet would automatically select:
'Excel Noob' from the picklist.
Not sure if this is possible or not.
Thanks for your time!
P.S I can't do this in SQL as the dataset doesn't allow for it.
- OliverScheurichGold Contributor
=IF(ISNUMBER(SEARCH($B$2,D2)),$A$2,IF(ISNUMBER(SEARCH($B$3,D2)),$A$3,IF(ISNUMBER(SEARCH($B$4,D2)),$A$4,"")))
Is this similar to what you are looking for?
Sub picklist() Dim i, j, k As Long Range("E:E").Clear j = Range("D" & Rows.Count).End(xlUp).Row For i = 2 To j For k = 2 To 4 If InStr(Cells(i, 4), Cells(k, 2).Value) Then Cells(i, 5).Value = Cells(k, 1).Value Else End If Next k Next i End Sub
An alternative could be these lines of code. In the attached file you can click the button in cell H2 to run the macro that enters the results in column E.