Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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" wrote in message oups.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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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" wrote in message oups.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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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" wrote in message ... 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 *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Check
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 ... 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" wrote in message ... 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get a conditional argument to check all cells in a array? | Excel Worksheet Functions | |||
how do I check if known number is in array | Excel Discussion (Misc queries) | |||
Check if first character of string is in array | Excel Programming | |||
Check if a String is inside an Array | Excel Programming | |||
Check if a String is inside an Array | Excel Programming |