Array Check

[NacellesEng]

I have a lot of code, but the critical portion of it is an array check:

Dim loadcases() As Variant
loadcases = Array(1010!, 1020!.....etc)
Do Until Mid(ActiveCell.Value, 3, 4) = loadcases(i)
If ActiveCell.Value = "END" Then Exit Do
ActiveCell.Offset(1, 0).Select
Loop

I manually assign values to my array as show of 4 digit numbers and
then check a cell for the matching value. The cells actual value looks
something like this:
LC10101000000000

Why is is that can not find the match? I have checked both my MID
statement with a MsgBox to make sure it was pulling the 1010 and I
checked the loadcase(i) to make sure that also had 1010. But I never
finds the match. Any ideas? Thanks!

[Bernie Deitrick]

NacellesEng,

What is it that you are trying to do? I'm sure that if you explain your
desired result, we could produce better code than looping through cells and
selecting them....

HTH,
Bernie
MS Excel MVP


"NacellesEng" <barwija@voughtaircraft.com> wrote in message
news:1114617317.273975.286220@g14g2000cwa.googlegroups.com...
> I have a lot of code, but the critical portion of it is an array check:
>
> Dim loadcases() As Variant
> loadcases = Array(1010!, 1020!.....etc)
> Do Until Mid(ActiveCell.Value, 3, 4) = loadcases(i)
> If ActiveCell.Value = "END" Then Exit Do
> ActiveCell.Offset(1, 0).Select
> Loop
>
> I manually assign values to my array as show of 4 digit numbers and
> then check a cell for the matching value. The cells actual value looks
> something like this:
> LC10101000000000
>
> Why is is that can not find the match? I have checked both my MID
> statement with a MsgBox to make sure it was pulling the 1010 and I
> checked the loadcase(i) to make sure that also had 1010. But I never
> finds the match. Any ideas? Thanks!
>

[Jason Barwig]

I have a very large excel file full of loadcases with corresponding
loads. I am writing some quick code to make my life easier to parse
through 30 different excel files one at a time and look for maximum and
minimum loads. There are 31 different loadcases and they are not all in
each file. So my code open each file, checks to see if the loadcase
identifier (the 4 digit code in the array) exists in the file, and if it
does then checks for max and mins in 4 different sets of elements
looking for the total max and min. There is a lot of data. So the loop
I am using is after the file has been opened, the first array value is
pulled out and checked against the 4 digit integer I am grabing with the
MID function. I need to work my way all the way through the file to
make sure it exists. It stops on the first find and then preforms more
code which again loops through the rest of the document and checks the
max and mins at the other 4 element groups. The total code works just
fine if I first assign each array integer to a dummy integer variable.
If I only managed to confuse you more I am sorry. Thanks for your help.

Jason


*** Sent via Developersdex http://www.developersdex.com ***

[Tom Ogilvy]

Your comparing a single to a string.

Dim loadcases() As Variant
loadcases = Array(1010!, 1020!.....etc)
Do Until Mid(ActiveCell.Value, 3, 4)) = format(loadcases(i)),"0000")
If ActiveCell.Value = "END" Then Exit Do
ActiveCell.Offset(1, 0).Select
Loop

--
Regards,
Tom Ogilvy


"NacellesEng" <barwija@voughtaircraft.com> wrote in message
news:1114617317.273975.286220@g14g2000cwa.googlegroups.com...
> I have a lot of code, but the critical portion of it is an array check:
>
> Dim loadcases() As Variant
> loadcases = Array(1010!, 1020!.....etc)
> Do Until Mid(ActiveCell.Value, 3, 4) = loadcases(i)
> If ActiveCell.Value = "END" Then Exit Do
> ActiveCell.Offset(1, 0).Select
> Loop
>
> I manually assign values to my array as show of 4 digit numbers and
> then check a cell for the matching value. The cells actual value looks
> something like this:
> LC10101000000000
>
> Why is is that can not find the match? I have checked both my MID
> statement with a MsgBox to make sure it was pulling the 1010 and I
> checked the loadcase(i) to make sure that also had 1010. But I never
> finds the match. Any ideas? Thanks!
>

[Jim Thomlinson]

Why is loadcases a variant? I ask because I think that the varaible type is
important here. Mid returns a string but your array is going to be full of
variants. This could be the source of your problem. Make sure that you are
comparing strings to strings or integers to integers... Based on your variant
declaration I would guess string to string but hard to tell from here...

HTH

"NacellesEng" wrote:

> I have a lot of code, but the critical portion of it is an array check:
>
> Dim loadcases() As Variant
> loadcases = Array(1010!, 1020!.....etc)
> Do Until Mid(ActiveCell.Value, 3, 4) = loadcases(i)
> If ActiveCell.Value = "END" Then Exit Do
> ActiveCell.Offset(1, 0).Select
> Loop
>
> I manually assign values to my array as show of 4 digit numbers and
> then check a cell for the matching value. The cells actual value looks
> something like this:
> LC10101000000000
>
> Why is is that can not find the match? I have checked both my MID
> statement with a MsgBox to make sure it was pulling the 1010 and I
> checked the loadcase(i) to make sure that also had 1010. But I never
> finds the match. Any ideas? Thanks!
>
>

[Alok]

Hi
Could be because your code does not increment the value of i in the loop?

"NacellesEng" wrote:

> I have a lot of code, but the critical portion of it is an array check:
>
> Dim loadcases() As Variant
> loadcases = Array(1010!, 1020!.....etc)
> Do Until Mid(ActiveCell.Value, 3, 4) = loadcases(i)
> If ActiveCell.Value = "END" Then Exit Do
> ActiveCell.Offset(1, 0).Select
> Loop
>
> I manually assign values to my array as show of 4 digit numbers and
> then check a cell for the matching value. The cells actual value looks
> something like this:
> LC10101000000000
>
> Why is is that can not find the match? I have checked both my MID
> statement with a MsgBox to make sure it was pulling the 1010 and I
> checked the loadcase(i) to make sure that also had 1010. But I never
> finds the match. Any ideas? Thanks!
>
>

[Bernie Deitrick]

Jason,

One immediate improvement might be to use the Find method, instead of
stepping through your cells. For example, if you wanted to find your value
in column C: (with loadcases already defined, as a global array) You can
add looping ot porcess multiple workbooks: post back if you need help doing
that.

Sub Macro1()

Dim myCell As Range
Dim FirstAddress As String
Dim mySht As Worksheet
Dim i As Integer

For Each mySht In ActiveWorkbook.Worksheets

With mySht.Columns("C:C")
For i = LBound(LoadCases) To UBound(LoadCases)
Set myCell = .Find(What:=LoadCases(i), _
LookIn:=xlValues, _
lookAt:=xlPart)
If Not myCell Is Nothing Then
FirstAddress = myCell.Address
MsgBox myCell.Address & " contains " & LoadCases(i)
Else
GoTo NotFound
End If

Set myCell = .FindNext(myCell)
If Not myCell Is Nothing And _
myCell.Address <> FirstAddress Then
Do
MsgBox myCell.Address & " contains " & LoadCases(i)
Set myCell = .FindNext(myCell)
Loop While Not myCell Is Nothing And _
myCell.Address <> FirstAddress
End If
Next i
End With
NotFound:
Next mySht
End Sub

HTH,
Bernie
MS Excel MVP


"Jason Barwig" <barwija@voughtaircraft.com> wrote in message
news:%23Chgsr0SFHA.2304@tk2msftngp13.phx.gbl...
> I have a very large excel file full of loadcases with corresponding
> loads. I am writing some quick code to make my life easier to parse
> through 30 different excel files one at a time and look for maximum and
> minimum loads. There are 31 different loadcases and they are not all in
> each file. So my code open each file, checks to see if the loadcase
> identifier (the 4 digit code in the array) exists in the file, and if it
> does then checks for max and mins in 4 different sets of elements
> looking for the total max and min. There is a lot of data. So the loop
> I am using is after the file has been opened, the first array value is
> pulled out and checked against the 4 digit integer I am grabing with the
> MID function. I need to work my way all the way through the file to
> make sure it exists. It stops on the first find and then preforms more
> code which again loops through the rest of the document and checks the
> max and mins at the other 4 element groups. The total code works just
> fine if I first assign each array integer to a dummy integer variable.
> If I only managed to confuse you more I am sorry. Thanks for your help.
>
> Jason
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

[Tom Ogilvy]

Using Find as shown,
LC20101000000000

would be considered a postitive match for 1010!

Perhaps you need to check potential matches to see if they are actual
matches.

--
Regards,
Tom Ogilvy



"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%23lk4bwBTFHA.3544@TK2MSFTNGP12.phx.gbl...
> Jason,
>
> One immediate improvement might be to use the Find method, instead of
> stepping through your cells. For example, if you wanted to find your value
> in column C: (with loadcases already defined, as a global array) You can
> add looping ot porcess multiple workbooks: post back if you need help
doing
> that.
>
> Sub Macro1()
>
> Dim myCell As Range
> Dim FirstAddress As String
> Dim mySht As Worksheet
> Dim i As Integer
>
> For Each mySht In ActiveWorkbook.Worksheets
>
> With mySht.Columns("C:C")
> For i = LBound(LoadCases) To UBound(LoadCases)
> Set myCell = .Find(What:=LoadCases(i), _
> LookIn:=xlValues, _
> lookAt:=xlPart)
> If Not myCell Is Nothing Then
> FirstAddress = myCell.Address
> MsgBox myCell.Address & " contains " & LoadCases(i)
> Else
> GoTo NotFound
> End If
>
> Set myCell = .FindNext(myCell)
> If Not myCell Is Nothing And _
> myCell.Address <> FirstAddress Then
> Do
> MsgBox myCell.Address & " contains " & LoadCases(i)
> Set myCell = .FindNext(myCell)
> Loop While Not myCell Is Nothing And _
> myCell.Address <> FirstAddress
> End If
> Next i
> End With
> NotFound:
> Next mySht
> End Sub
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jason Barwig" <barwija@voughtaircraft.com> wrote in message
> news:%23Chgsr0SFHA.2304@tk2msftngp13.phx.gbl...
> > I have a very large excel file full of loadcases with corresponding
> > loads. I am writing some quick code to make my life easier to parse
> > through 30 different excel files one at a time and look for maximum and
> > minimum loads. There are 31 different loadcases and they are not all in
> > each file. So my code open each file, checks to see if the loadcase
> > identifier (the 4 digit code in the array) exists in the file, and if it
> > does then checks for max and mins in 4 different sets of elements
> > looking for the total max and min. There is a lot of data. So the loop
> > I am using is after the file has been opened, the first array value is
> > pulled out and checked against the 4 digit integer I am grabing with the
> > MID function. I need to work my way all the way through the file to
> > make sure it exists. It stops on the first find and then preforms more
> > code which again loops through the rest of the document and checks the
> > max and mins at the other 4 element groups. The total code works just
> > fine if I first assign each array integer to a dummy integer variable.
> > If I only managed to confuse you more I am sorry. Thanks for your help.
> >
> > Jason
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>