Excel VBA Find data in two columns on a single row

Question

I'm trying to make this code find a row where column V is not equal to "Y" or "L" AND column A is not blank. I think I'm overdoing it a bit, I'm sure there is an easier way to check both cells on the row.

Sub EndMove()
Dim Col1 As Integer, Col2 As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String, currentRowValue2 As String

Col1 = 22
Col2 = 1
rowCount = Cells(Rows.Count, Col1).End(xlUp).row

For currentRow = 1 To rowCount
    currentRowValue = Cells(currentRow, Col1).Value
    If currentRowValue <> "y" Or currentRowValue <> "l" Then
    currentRowValue2 = Cells(currentRow, Col2).Value
    If Not IsEmpty(currentRowValue2) Then
    Cells(currentRow, Col1).Select
    MsgBox "Move this?"
End If
End If
Next

End Sub

Thanks

What do you want to do with the data when you find it? Ie., Count it? Sum it? Return a value? — ashleedawg
– ashleedawg, Commented Sep 3, 2018 at 22:35
@Bofett maybe you should explain more about what your code is meant to do (and possibly the other sub). You do not need to Select anything as stated in my comment. Chances are, you are just using Select as a middle operator. You already have an answer to your question here so your next problem deserves a new question imo. — urdearboy
– urdearboy, Commented Sep 3, 2018 at 22:56
@urdearboy, you are right. I appreciate your help, this works perfect. I will work on the section and start a new question if needed. — Bofett
– Bofett, Commented Sep 3, 2018 at 23:05

urdearboy · Accepted Answer · 2018-09-03 22:49:09Z

1

You were close. I changed currentrow to i since it is easier to use multiple times. You should also qualify your sheet. Anytime you are referring to an object on the target sheet, qualify it with ws

It's also worth nothing that this is case sensitive. I.E. Y <> y. If you want this to ignore case you can put Option Compare Text above Sub EndMove

Option Explicit

Sub EndMove()
Dim rowCount As Long, i As Long

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'i refers to row number
For i = 11 To rowCount
    If ws.Range("V" & i) <> "Y" And ws.Range("V" & i) <> "L" Then
        If ws.Range("A" & i) <> "" Then
            'Do what with row i?
        End If
    End If
Next i

End Sub

You could also combine all 3 of your criteria into one line like so

For i = 11 To rowCount
    If ws.Range("V" & i) <> "Y" And ws.Range("V" & i) <> "L" And ws.Range("A" & i) <> "" Then
        'Do what with row i?
    End If
Next i

edited Sep 3, 2018 at 22:49

answered Sep 3, 2018 at 22:35

urdearboy

14.6k6 gold badges35 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bofett Over a year ago

Much better, thanks! Can you please help me with ignoring case? I'd like it to look for upper and lower case. Also, is there an easy way to start looking at row 11 rather than the beginning? Thanks again!

urdearboy Over a year ago

Can you elaborate? Do you want to know if it is Y, y, L, or l? If so, I addressed that in the solution. Add Option Compare Text at the top and it will treat upper case and lower case equally

urdearboy Over a year ago

Updated to start at row 11. Just change For i = 1 to to For i = 11 to . Again, i and currentrow are the same. Every time you see i, think row number

Bofett Over a year ago

Thanks again, I need to select the cell in column A then call a sub.

urdearboy Over a year ago

You do not need to Select! What do you really need to do?

|

Collectives™ on Stack Overflow

Excel VBA Find data in two columns on a single row

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related