Instr function with two conditions

Question

I have a string in the cell "A1" and another string in the cell "A2" of Sheet2 which I take them using the LEFT Function. These are changing at every import. I'm trying to find the first string in the column "AP" and the second string in the column "AA" of Sheet1 and sort the sheet by these values. Then I want to copy the entire Sheet1 and paste it in Sheet2. My code gives me nothing. Why is wrong?

Sub rc1()
    Dim lastrow As Long
    Dim i As Integer, icount As Integer
    Dim j As Integer, jcount As Integer
    Dim LResult As String

    LResult = Sheets("Sheet2").Range("A1")
    LResult = Left(LResult, 4)
    JResult = Sheets("Sheet2").Range("A2")
    JResult = Left(JResult, 2)

    lastrow = Sheets("Sheet1").Range("A30000").End(xlUp).Row

    Sheets("Sheet2").Activate
    Sheets("Sheet2").Range("B2:AQ" & lastrow).Select
    Selection.ClearContents

    icount = 1
    For i = 2 To lastrow
        For j = 2 To lastrow
            If InStr(1, LCase(Sheets("Sheet1").Range("AP" & i)), LCase(LResult)) <> 0 And InStr(1, LCase(Sheets("Sheet1").Range("AA" & j)), LCase(JResult)) <> 0 Then
                icount = icount + 1
                Sheets("Sheet2").Range("B" & icount & ":AQ" & icount) = Sheets("Sheet1").Range("A" & i & ":AP" & i).Value
            End If
        Next j
    Next i
End Sub

Why have you chosen to make some if your variables `Integer` rather than `Long` ?? — Gary's Student, Jun 26 '19 at 12:24
I am new in this area and I don't know which is better to use. — Mazilu Isabela Elena, Jun 26 '19 at 12:27
@MaziluIsabelaElena Row counting variabes have to be `Long` because Excel has more rows than fit into `Integer`. In general there is no benefit of using `Integer` in VBA. — Pᴇʜ, Jun 26 '19 at 12:29
@Gary'sStudent, I put Long instead of Integer, but nothing changed. — Mazilu Isabela Elena, Jun 26 '19 at 12:34
@Pᴇʜ Doesn't current VBA automatically convert `Integer` (2-byte) to `Long` (4-byte), while maintaining the range limit of the dimensioned variable ( -33k ~ +33k) ? That would make it inherently bad to use `Integer` in this day and age, no? — Cyril, Jun 26 '19 at 12:55
@Cyril Yes, it does. See [Why Use Integer Instead of Long?](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) — Pᴇʜ, Jun 26 '19 at 12:58
@Pᴇʜ *purely tangential comment/question in reflection of that post* (also thanks for posting that, I thought I had read that elsewhere, but apparently it was on SO)... the use of `Variant` versus `Long` might be the question when it comes to dimensioning? I believe `Variant` will dimension as 12-byte, so if you're not simply looping through the rows/columns in Excel, where `Long` is appropriate, e.g., summing/multiplying values within a loop, then `Dim i as Variant` would be an appropriate approach, yes? — Cyril, Jun 26 '19 at 13:10
@Cyril Actually `Variant` is the worst type you can choose (only use it if you cannot be more explicit). It is always better no find the type that is more explicit to your data. *"Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer."* That means a Variant can be Variant/Integer or Variant/Long etc. — Pᴇʜ, Jun 26 '19 at 13:15
Where are you defining `JResult`? You defined `lastrow`, `I`, `icount`, `j`, `jcount` and `LResult` - make sure that *every* module has `Option Explicit` at the top. Tools > Options > Require Variable Declaration will do that automatically for you. If you step through manually line-by-line with F8, at which point does it do something different to what you want? Put a Break Point on `icount = icount + 1` to see if that `IF` statement is ever `True` — Chronocidal, Jun 27 '19 at 08:52

score 0 · Answer 1 · answered Jun 26 '19 at 12:50

0

I think your problem is here:

LResult = Sheets("Sheet2").Range("A1")
LResult = Left(LResult, 4)
JResult = Sheets("Sheet2").Range("A2")
JResult = Left(LResult, 2)

should that last line not be JResult = Left(JResult, 2) ?

the way it works at the moment, it will overwrite the value you assign from sheet2 cell A2, with the left most 2 characters in sheet2 cell A1. If this value then doesnt appear in column AA, then the criteria in your if statement will never evaluate as true.

answered Jun 26 '19 at 12:50

DMcLaren

75
1
5

@MaziluIsabelaElena Chronocidal's comment on your question would be the next thing I would try. If your criteria in your IF statement are never met then you won't find any results. – DMcLaren Jun 27 '19 at 10:21

Instr function with two conditions

1 Answers1