Anbody knows a more efficient way of doing this search?

[jackeroo75]

I query the data from our database and copied it to Excel. I compare the sheet and if it matches then copies it to another column. It takes forever to do this. Is there a better way of programming this? Should I store it an array?? help..


For k = (g_startRow + 1) To (endofRow - 1) 'Get range
stringlength = Len(Cells(k, 1).Value) 'Get string's length

If Left(Cells(k, 1).Value, 2) Like "49*" Then
Cells(k, 26).Value = Left(Cells(k, 1).Value, 2)
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
ElseIf Cells(k, 1).Value Like "WAIT/WR" Then
Cells(k, 26).Value = "ZZ"
Cells(k, 27).Value = "ZZ"
ElseIf Left(Cells(k, 1).Value, 2) Like "4-*" Then
Cells(k, 26).Value = Left(Cells(k, 1).Value, 2)
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
ElseIf Left(Cells(k, 1).Value, 2) Like "R*" Then
Cells(k, 26).Value = Left(Cells(k, 1).Value, 2)
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
ElseIf Left(Cells(k, 1).Value, 2) Like "1-*" Then
Cells(k, 26).Value = Left(Cells(k, 1).Value, 2)
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
ElseIf Left(Cells(k, 1).Value, 2) Like "3-*" Then
Cells(k, 26).Value = Left(Cells(k, 1).Value, 2)
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
ElseIf Left(Cells(k, 1).Value, 2) Like "11*" Then
Cells(k, 26).Value = Val(Left(Cells(k, 1).Value, 2))
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
ElseIf Left(Cells(k, 1).Value, 2) Like "10*" Then
Cells(k, 26).Value = Val(Left(Cells(k, 1).Value, 2))
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
Else
Cells(k, 26).Value = Val(Left(Cells(k, 1).Value, 1))
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
End If

Next k

 

[MakeItSo]

Hi Jackeroo,

1.) Why use LIKE? If you query the first two characters only, then "LIKE "49*"" is nonsensical. Use "="49"" instead
Same for your other conditions.

2.) The operations are the same for most of your conditions. So instead of using multiple IFs rather use select to cover multiple matches:

If Cells(k, 1).Value Like "WAIT/WR" Then
    Cells(k, 26).Value = Left(Cells(k, 1).Value, 2)
    Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)
Else
    Select Case Left(Cells(k, 1), 1)
        Case "4", "R", "3"
            Cells(k, 26) = Left(Range(Cells(k, 1)).Text, 2)
            Cells(k, 27) = Range(Cells(k, 1)).Text
        Case "1"
            Cells(k, 26) = Val(Left(Range(Cells(k, 1)).Text, 2))
            Cells(k, 27) = Range(Cells(k, 1)).Text
        Case Else
            Cells(k, 26) = Val(Left(Range(Cells(k, 1)).Text, 1))
            Cells(k, 27) = Range(Cells(k, 1)).Text
    End Select
End If

Or the like.

3.) You say "I query the data from our database and copied it to Excel. I compare the sheet..."

Why not add another column to your query and do the operation right in the database? Why do you take the workaround via Excel first?
Give us a clue what you are actually trying to perform. I am sure there must be a more efficient way to do it.

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jackeroo75]

I tried your code, but it's giving me a runtime error of
1004 with the message: 'Range' of objects _Global failed.

 

[MakeItSo]

You are aware, that you still need the "For k=..." loop wrapped around that code? Else Cells(k,1) won't make any sense.
besides that: when you encounter an error, also tell where the error occurs. Without such info, all we do is plain guessing.

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jackeroo75]

Yes, it seems the problem lies with this statement

Cells(k, 27) = Range(Cells(k, 1)).Text

 

[PHV]

Replace this:
Cells(k, 27) = Range(Cells(k, 1)).Text
with this:
Cells(k, 27) = Cells(k, 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jackeroo75]

Thanks PHV

I knew, that didn't work with range.

Also thanks MakeITso for your case statements.

 

[MakeItSo]

Argh, should have tested it!
Of course it DOES work with range. As you were fiddling around with "stringlength", I thought you wanted to make sure just to use the text contents of that cell, without any formatting. However, Range needs an address, so it should have been rather

Range(Cells(k, 1)[b].Address[/b]).Text

Anyway, if using the cell's content directly works fine with you then that is fully sufficient.
Thanks PHV.

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jackeroo75]

My question: Does changing it to range is really saving time or resource?
Another question, In order for my range to work I had to change my value from 1 to 5 why?

Cells(k, 26).Value = Val(Range(Left((Cells(k, 1).Address), 5)))
Cells(k, 27).Value = Range(Left((Cells(k, 1).Address), stringlength))

is equivalent as this

Cells(k, 26).Value = Val(Left(Cells(k, 1).Value, 1))
Cells(k, 27).Value = Left(Cells(k, 1).Value, stringlength)

Example value: "7SUD/10"
Stringlength is 7

Cells(k, 26).Value = Val(Range(Left((Cells(k, 1).Address), 5))) = 7

same as

Cells(k, 26).Value = Val(Left(Cells(k, 1).Value, 1))

Why???

 

[PHV]

Why
Simply have a look at how the parenthesis are balanced ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886