0

I have two loops going through rows and columns in excel with a large if or statement checking if there's a certain string in one of the columns. However, even though I've added Option Compare Text to get rid of case sensitivity, sometimes my code will skip over columns even if they have the specified text in them.

Right now, my statement looks like this:

    If worksheet.Cells(j, i).Text="text" or worksheet.Cells(j, i).Text = "other text"...

I figured it could be that spaces are causing the problem, but after testing, it seems arbitrary. Is there something wrong with my If statement, or is there possibly something else in the excel sheets it's not working on that I haven't covered? (Like text being italicized or bold).

For the record, I'm not using like or instr because some of the columns may contain pieces of the string but may not be what I'm looking for.

PointXIV
  • 1,258
  • 2
  • 15
  • 23
  • 1
    try to change `Cells(j, i).Text` to `Cells(j, i).Value` – Dmitry Pavliv Feb 14 '14 at 18:51
  • Thanks, that works. Do you have an explanation why .value works better than .text? I found [this](http://www.baldyweb.com/ValueText.htm) but it didn't seem like it would affect what I'm doing. – PointXIV Feb 14 '14 at 20:17
  • 1
    so, `.Text` property returns your data in cell according to your format. Say, if value of cell is `5`, but it formatted to be `5.00$`, `.Text` will return `5.00$`, but `.Value` would return `5`. This is the main difference – Dmitry Pavliv Feb 14 '14 at 20:20
  • Check **[This](http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2)**, pretty interesting! – simpLE MAn Feb 14 '14 at 20:21

2 Answers2

1

try to change Cells(j, i).Text to Cells(j, i).Value

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
0

Try:

If Trim(LCase(Worksheet.Cells(j, i).Text)) = "text" Or _
Trim(LCase(Worksheet.Cells(j, i).Text)) = "other text" Then
simpLE MAn
  • 1,582
  • 13
  • 22