# Find cells in range that are not blank and add value



## Glasgowsmile (Dec 20, 2022)

I'm trying to look through Column B and if a value exists in Column B then I want to add 'Yes/No' to the corresponding row in column A.

This code just adds Yes/No to the entire range but how would I make it specific to what's only got a value in Column B? My assumption is that I need a for loop but I'm not familiar with those for VBA.


```
If Not IsEmpty(Range("B2:B25")) Then
        Range("A2:A25").Value = "Yes / No"
    End If
```

Example of what I'm trying to do:

Column AColumn BYes / NoValue 1Yes / NoValue 2Yes / NoValue 3* Remains Blank ** Remains Blank *


----------



## Rick Rothstein (Dec 20, 2022)

If there are no headers in Column B, then you can use whichever of these single lines of code (within whatever Sub you are using) to do what you asked for...

If your values are constants (not formulas)
-------------------------------------------------------------
Columns("B").SpecialCells(xlConstants).Offset(, -1) = "Yes/No"

If your values are formulas (not constants)
-------------------------------------------------------------
Columns("B").SpecialCells(xlFormulas).Offset(, -1) = "Yes/No"


----------



## Glasgowsmile (Dec 20, 2022)

Would one of these lines be added within the If statement or is the if statement no longer required? I tried this the xlFormulas one and it worked for the first row but left the rest of the row blank. The formula is a filter formula, does that matter in this case?


----------



## Rick Rothstein (Dec 20, 2022)

I'm sorry, my bad. I tested for constants... for formulas, I need to know what kind of values are in Column B, numbers or text?


----------



## Glasgowsmile (Dec 20, 2022)

Rick Rothstein said:


> I'm sorry, my bad. I tested for constants... for formulas, I need to know what kind of values are in Column B, numbers or text?


It's a filter formula so I think it only see's the first row as having a formula and the rest are grayed out. It's just text otherwise.


----------



## Rick Rothstein (Dec 20, 2022)

I cannot test this because my version of XL365 (the free online version) does not support VBA, but from what I read, I think this is how you would do it...

Range("B2#").SpecialCells(xlFormulas, xlTextValues).Offset(, -1) = "Yes/No"

I am pretty sure to reference a spilled range, you reference the first cell (the one with the formula) and add a # sign after it.


----------



## Glasgowsmile (Dec 20, 2022)

Rick Rothstein said:


> I cannot test this because my version of XL365 (the free online version) does not support VBA, but from what I read, I think this is how you would do it...
> 
> Range("B2#").SpecialCells(xlFormulas, xlTextValues).Offset(, -1) = "Yes/No"
> 
> I am pretty sure to reference a spilled range, you reference the first cell (the one with the formula) and add a # sign after it.


Didn't work but no errors, just only adding it to the first row and not the other rows where the spill of the formula goes.

I just found that this worked:

```
Range("B2#").Offset(, -1) = "Yes/No"
```


----------



## Rick Rothstein (Dec 20, 2022)

You definitely have the formula returning text values and not numeric values, correct?
Also, describe "didn't work" please.


----------

