Changing a nested IfThen equation into VBA macro for a range of cells

[WAB13]

I currently have a nested IfThen equation for a range of cells where depending on certain words in the first column of data (Which is pasted in by the user) different values are put into the second column of data. So for example, if the word "Ladder" was in B20 the words "allelic ladder" would show up in F20, as well as if the letters "POS" were in B17, the words "Positive control" would show up in F17. I was wondering if there was a way to make a macro in VBA to do this exact thing without having to write it for every individual cell. I've tried doing a Case function using InStr to scan the value of the cell for the different words, but I can only seem to do that for a singular cell and not for a range of cells without rewriting the code for each cell since its looking a specific sting value and not a range of them. If also tried just making a basic If Else macro, but again, I'm not sure how to have it automatically do it for a range of cells. I know I could just loop the macro for the one cell and have it do it for all the others, but I didn't know if there was a way to do it without that.

Attached is a photo of the macro I have and an explanation for what I want to do. I'm not sure if this is possible the the current way I'm trying to do it or if it's even possible at all as I don't really know too much about VBA.

Thanks!

 

[johnnyL]

There are many ways you can accomplish your goal, the quickest way would probably be to have a column of values that the user might input and another column with the values that you would want to put into Column F.

 

[WAB13]

There are many ways you can accomplish your goal, the quickest way would probably be to have a column of values that the user might input and another column with the values that you would want to put into Column F.

There would be an infinite possible values the user might enter, but they would fall into one of four categories.

 

[johnnyL]

It sounds like you have left out some information needed to assist you. There is no way to code for infinite possible of values from the user. There has to be a pattern of something to be able to identify what will be written to the F column.

 

[johnnyL]

Does the picture that you included contain all of the checks that would be made for each cell? If that is the case, then we probably have enough to go on. I wasn't sure if that was just a partial sample of what you were trying to do.

Another question, is B17:B30 & F17:F30 the approximate ranges involved, or would it be much larger?

 

[johnnyL]

If your picture does reflect everything that you are wanting to do, the following code should do what you want:

  With Range("B17:B30")
    .Offset(, 4) = Evaluate("IF(ISNUMBER(FIND(""Ladder""," & .Address & ")),""Allelic Ladder"",IF(" & .Offset(, 4).Address & "="""",""Sample"",""Sample""))")
    .Offset(, 4) = Evaluate("IF(ISNUMBER(FIND(""POS""," & .Address & ")),""Positive Control""," & .Offset(, 4).Address & ")")
    .Offset(, 4) = Evaluate("IF(ISNUMBER(FIND(""NEG""," & .Address & ")),""Negative Control""," & .Offset(, 4).Address & ")")
   End With

That will go through each cell, without the looping that you mentioned.

 

[Alex Blakenburg]

I haven't looked to closely at your code but just addressing your Select Case, your syntax looks wrong.
I would expect the first line to say "Select Case True"

Here is an example:
Can Wild Card Characters be used in VBA Select Case statements

A longer version is here:
Excel VBA Select Case Like Wildcard in 5 Steps (+Workbook Example)