Solved

Find Value in a Row in Excel

Posted on 2015-01-12
4
58 Views
Last Modified: 2015-01-12
I have a spreadsheet and need to search a range in each row looking for a value (0). Example: Look in F12:P12 and if a 0 is found in any cell return 0 Else 1.
0
Comment
Question by:EMCIT
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40544387
Sub DoIt()
For Each cel In Selection
    If cel.Value = "0" Then
        myAnswer = 0
        Exit For
    Else
        myAnswer = 1
    End If
Next
MsgBox myAnswer
End Sub

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40544389
=IF(COUNTIF(F12:P12,0),0,1)
should do it.
0
 
LVL 11

Author Comment

by:EMCIT
ID: 40544409
=IF(COUNTIF(F12:P12,0),0,1) works great. I have another column that needs to search specific columns as well.
Example: G12, I12, J12, M12, N12 only but when I try to string the cells it doesn't work.
0
 
LVL 11

Author Comment

by:EMCIT
ID: 40544441
I got it!

=IF(SUM(G22,I22,J22,M22,N22)=COUNT(G22,I22,J22,M22,N22),1,0)

Thank you for your assistance.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question