Checking if the value of a cell is a specific string

Question

This one is driving me insane.

I want to check see if a cell's value is "Yes", but does not matter what I try to do it, VBA keeps saying type mismatch.

Really can use some help here.

Here is my code:

Dim rowcntr As Variant
Dim rowcntr2 As Variant
Dim i As Integer
For i = 1 To rowcntr
    rowcntr2 = WorksheetFunction.CountA(Sheet3.Range("A:A"))
    If sheet10.Range("R1").Offset(i, 14).Find("Yes", sheet10.Range("R1"), xlValues, xlWhole, xlByColumns, xlNext, True) Is Nothing Then
        i = i + 1
    Else
        Sheet3.Range("A1").Offset(rowcntr2, 0) = sheet10.Range("R1").Offset(i, 0)
        Sheet3.Range("A1").Offset(rowcntr2, 1) = sheet10.Range("R1").Offset(i, 1)
        Sheet3.Range("A1").Offset(rowcntr2, 2) = sheet10.Range("R1").Offset(i, 2)
        Sheet3.Range("A1").Offset(rowcntr2, 3) = sheet10.Range("R1").Offset(i, 3)
        Sheet3.Range("A1").Offset(rowcntr2, 4) = sheet10.Range("R1").Offset(i, 5)
    End If
Next i

Can you explain what exactly you're trying to do? One red flag - you're modifying `i` within the loop with `i = i + 1`. Also not sure if you need `Range.Find` for this. — BigBen, Oct 04 '19 at 12:38
And `Dim i as Integer` should be `Dim i as Long`. And `rowcntr2` should be `Long` as well. — BigBen, Oct 04 '19 at 12:40
It seems like the way you're using them, `rowcntr` and `rowcntr2` should also be `Long`. — riskypenguin, Oct 04 '19 at 12:41
rowcntr = WorksheetFunction.CountIf(sheet10.Range("AF:AF"), "<>" & "") — P. MAJ, Oct 04 '19 at 12:48
What line throws the error? That `rowcntr` line? (which should be in your question, not in the comments). — BigBen, Oct 04 '19 at 12:52
what im trying to do is to check if a specific column in sheet10 contains yes, if so, then copy the cells to sheet3. so if a row in sheet10 has another value in that column (offset 14) instead of "Yes", it would skip that and move to the next row. — P. MAJ, Oct 04 '19 at 13:05
nope, the if statement, where i want to check if the value of the cell is "Yes". — P. MAJ, Oct 04 '19 at 13:05
well i am trying to have it automated, so data will be added by the user and if it was what i was after then it would be copied into sheet3 — P. MAJ, Oct 04 '19 at 13:06
You can use a filter instead of looping row by row. And from your explanation it sounds like you don't need `Find` at all. — BigBen, Oct 04 '19 at 13:07
i also tried If sheet10.Range("R1").Offset(i, 14).value = "Yes" then but it keeps saying type mismatch. i tried to take the .value off same problem. how can i use a filter? — P. MAJ, Oct 04 '19 at 13:09
Something like the approach in [this question](https://stackoverflow.com/questions/17531128/copy-paste-calculate-visible-cells-from-one-column-of-a-filtered-table). — BigBen, Oct 04 '19 at 13:11
unfortunately that is not what i am after. any way the value of a cell can be checked to a specified string in VBA? — P. MAJ, Oct 04 '19 at 13:18
Best way is `If cell.value="Yes"` but your approach is kind of confusing. Can you post a data example and your expected output? — Foxfire And Burns And Burns, Oct 04 '19 at 13:19
ok lets try it another way: shall i say i have a workbook, with a column A in a sheet. now the end user who unfortunately is not excel savvy, copies some data into column A. lets say city names. now i want if the city name was for example "York", to add a value in the corresponding cell in column B. how i go about that? the problem im having is that VBA looks into the value in column A, then says you can not compare it to "York" as the type is different. that is my problem here. — P. MAJ, Oct 04 '19 at 13:26
thanks everyone! could not have it done without you! God bless you all ^^ — P. MAJ, Oct 04 '19 at 13:54
Can you include a screenshot of `sheet10` and possibly `sheet3`? — John Alexiou, Oct 07 '19 at 13:41

score 0 · Answer 1 · answered Oct 04 '19 at 13:30

sorted it out myself...

Dim rowcntr As Variant

Dim rowcntr2 As Variant

Dim i As Integer

Dim j As Variant

rowcntr = WorksheetFunction.CountIf(sheet10.Range("AF:AF"), "<>" & "")

For i = 1 To rowcntr

    rowcntr2 = WorksheetFunction.CountA(Sheet3.Range("A:A"))

    j = CStr(sheet10.Range("R1").Offset(i, 14))

    If j = "Yes" Then

        Sheet3.Range("A1").Offset(rowcntr2, 0) = sheet10.Range("R1").Offset(i, 0)

        Sheet3.Range("A1").Offset(rowcntr2, 1) = sheet10.Range("R1").Offset(i, 1)

        Sheet3.Range("A1").Offset(rowcntr2, 2) = sheet10.Range("R1").Offset(i, 2)

        Sheet3.Range("A1").Offset(rowcntr2, 3) = sheet10.Range("R1").Offset(i, 3)

        Sheet3.Range("A1").Offset(rowcntr2, 4) = sheet10.Range("R1").Offset(i, 5)

    Else

        i = i + 1

    End If

Next i

It's great you worked it out yourself, but just have a look at some of the comments in the comment section as they can be valuable for your future endevours. — JvdV, Oct 04 '19 at 13:40
yes sir! just figured out that i = i + 1 was causing me a lot of headache. just removed it. thanks all! best website ever and best ppl ever ^^ — P. MAJ, Oct 04 '19 at 13:54

Checking if the value of a cell is a specific string

1 Answers1