Forum Discussion

RE_Lax1060's avatar
RE_Lax1060
Copper Contributor
Feb 15, 2023

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.

excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 15, 2023

    RE_Lax1060 

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

Resources