If cell contains specific text then push function to the cell to the right of it

[rdoulaghsingh]

Is there a way to add another condition to the code below to read if the cell it's targeting for the function is not blank then do not push the function? Thanks!

For Each cellAR In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If InStr(cellAR.Text, "City, ST, Zip") > 0 Then
cellAR.Offset(0, 1) = "=CONCATENATE('Introduction CMMC'!B17)"
End If
Next cellAR

 

[Joe4]

Try this:

    If InStr(cellAR.Text, "City, ST, Zip") > 0 And cellAR.Offset(0, 1)="" Then
        cellAR.Offset(0, 1) = "=CONCATENATE('Introduction CMMC'!B17)"
    End If
Next cellAR

 

[rdoulaghsingh]

Try this:

    If InStr(cellAR.Text, "City, ST, Zip") > 0 And cellAR.Offset(0, 1)="" Then
        cellAR.Offset(0, 1) = "=CONCATENATE('Introduction CMMC'!B17)"
    End If
Next cellAR

Thanks for your reply @Joe4. It doesn't seem to like it. I'm getting a Run-time error '13': Type mismatch.

 

[Joe4]

That isn't quite the code that I gave you, but do you have any values in column B that are errors?

 

[rdoulaghsingh]

That isn't quite the code that I gave you, but do you have any values in column B that are errors?

I changed the code slightly to say if the cell already has the company name in their don't inject the formula in the cell. I'm getting ref errors in column B that are empty.

 

[Joe4]

Are those errors there BEFORE or after you run the code?
If they are there before you run the code, you need to fix them first.
What is causing those errors?

Also, in the formula that you are trying to apply to column B via the VBA code, why are you trying to use the CONCATENATE function if you are only pulling a single value and not actually trying to Concatenate anything?

 

[rdoulaghsingh]

Are those errors there BEFORE or after you run the code?
If they are there before you run the code, you need to fix them first.
What is causing those errors?

Also, in the formula that you are trying to apply to column B via the VBA code, why are you trying to use the CONCATENATE function if you are only pulling a single value and not actually trying to Concatenate anything?

These occur after the code is run. So here's how it works. I have a cell that is connected to 2 different forms/sheets which capture company name from both. Ultimately, only 1 form will be used and the other deleted. This code is pushing the formula into the cell from the form which is not deleted. There are other tables with <Company Name> on that same sheet where the formula is being injected with a pre-filled company. What I'm trying to do is if the text value "Company" is in Col A And the cell to the right is not equal to "Company A", then it injects the formula in the cell to the right.

 

[Joe4]

If the line of code you highlighted in yellow is the one it is shwoing when you click "DEBUG" on that error, try getting rid of the ".TEXT" and see if that makes a difference.

If it does not, we will need to add some error handling to your code. Please post your entire procedure so we can do that, and be sure to copy/paste the actual code and use Code Tags (do NOT post an image of the code, I cannot work with that).

 

[rdoulaghsingh]

I managed to get it working. Thank you for your help.

 

[Joe4]

I managed to get it working. Thank you for your help.

What was the issue?