Trying to Automate a query

Copper Contributor

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.

1 Reply



Is this similar to what you are looking for?



Sub picklist()

Dim i, j, k As Long


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
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.