more array help needed

[needhelp]

thanks to Par Jessen I can now populate my arrays (previous
question)... however i have another issue with another array.

The array is dynamic (single dimension).

Each array element is the count of cells
e.g.
numpkfrows(1) = 216
numpkfrows(2) = 219
numpkfrows(3) = 216
....numpkfrows(n)

i now need to determine if all of the values of the array elements are
the same (so in the above example I would want an error thrown).

(hope this makes sense)

What would be the easiest way to compare all of the elements within
the array?

Cheers
Julie

 

[Per Jessen]

Hi again Julie,

You have to loop through the array and test for differences. This
should do what you need:

Sub TestMe()
Dim NotSame As Boolean
MyValue = numpkfrows(LBound(numpkfrows))
For Itm = LBound(numpkfrows) To UBound(numpkfrows) - 1
If numpkfrows(Itm) <> numpkfrows(Itm + 1) Then
NotSame = True
Exit For
End If
Next
If NotSame Then
MsgBox ("Error")
End If
End Sub

Cheers
Per

 

[needhelp]

thanks Per (and i apologise for getting your name wrong in my previous
post).

i hope you don't mind but i have a further question that i would
appreciate your input on.

my situation is that i have a large number of worksheets to populate
from other worksheets,
each of these sheets can have a variable number of primary key fields,
which can be in any column
each sheet will have a variable number of populated rows
- i'm checking initially that they number of populated rows in each of
the primary key fields is the same - this is the code you've just
assisted with -
if they are i then need to check that each combination of primary key
fields are unique within that sheet

i'm having a problem visuallising the best solution ...

i was hoping to use arrays to do this with but am not sure that i can
use multidimensional arrays given that you can only redim the last
dimension.

would appreciate any input.

Cheers
Julie
PS i'm in a meeting for about an hour and a half, so apologies if i
don't get back to you if you respond in the meantime.

 

[Per Jessen]

Maybe you can use an advanced filter on your primary key fields table,
and filter for unique values only. Then if number of visible rows is
equal to number of rows in table, then each combination of primary key
fields are unique.

If you need help writing the macro, we need more information on the
layout of your sheet.

Cheers
Per

PS I'm going home after a night shift in a while, so I will not be
online for some hours.

 

[needhelp]

how do you do an advanced filter for unique values when the columns
you're filtering on (ie my primary key fields) aren't the whole table
and aren't in consecutive columns?

basic structure of the worksheets are

up to 137 columns of data
column A is static data (as is rows 1-3)
Row 4 is the field name
Row 5 is some information about the field
Row 6 contains the information as to whether or not the column is a
primary key field
Row 7 contains more information about the fields
Row 8 to nnnn contains the data

Each worksheet can have a multiple number of primary key fields
These are not necessarily consecutive columns
I can not rearrange the columns at all

i have extracted the information as to whether the column is a primary
key field to another workbook to make it easier to read (the
information in row 6 is not consistently structured)

my code so far (this section of it)
looks through the "primary key field" table stores the field names of
the primary key fields for this sheet in an array
then cycles through the sheet initially checking that these column are
populated
and that they have the same number of records

now i need to be able to determine if the combination of the primary
key fields are unique
in the worksheet i'm working on the two primary key fields are in
columns B & C (but this is not the case in the other sheets - and i
would like to have pretty generic code as i need to process over 100
sheets).

so, in english, i think i need to concatenate the values in B & C and
compare them to every other concatenated value in B & C
in another sheet, i need to concatenate the values in B, E, AA and
compare them to every other concatendated value in B, E, AA

and no, i can't add another column into the worksheet. I CAN copy the
information to another worksheet - but then i would need to be able to
find and highlight the data in this worksheet if there is duplicates.

any input would be appreciated.

Cheers
Julie

 

[Joel]

I'm notr sure what you are trying to do but I have written macros that merge
worksheets togeter where the rows and columns are different. I don't use
arrays to accomplish this task. Instead I use the find function. Below is a
summary of the code I use. This code is not tested. I just used to show an
example of what I usually do. the coe need to be adjusted depending on the
number of sheets yo are adding to the mater worksheet.

Using VBA functions like the FIND Method is sometimes quicker than working
with arrays in VBA. sorting and searching using VBA code is usally slower
that using stand methods and function methods that microsoft optimized. The
optimized code (like FIND) has been run through compilers that are designed
to make the code run quickly. The VBA code isn't optimized the same way
which makes the code run slower.

1) Start with blank worksheet which I use as a master worksheet. then
repeat this process for each worksheet/workbook I want to add to the master
sheet.

2) Each worksheet I add has an ID row in column A and a set of headers in
row 1.

3) I use two for loop to span each new worksheet I add to the master

NewRow = 2
NewCol = 2
For ColCount = 2 to LastColumn
ColumnHeader = cells(1,Colcount)
'check if header exists in master sheet
set c = MasterSht.Rows(1).find(what:=ColHeader)
if c is nothing then
AddCol = NewCol
NewCol = Newcol + 1
else
AddCol = c.column
end if
for RowCount = 2 to Lastrow
RowHeader = Range("A" & RowCount)
'check if header exists in master sheet
set c = MasterSht.Columns("A").find(what:=RowHeader)
if c is nothing then
AddRow = NewRow
NewRow = NewRow + 1
else
AddRow = c.column
end if

MasterSht.Cells(AddRow, AddCol) = Range(RowCount,ColCount)

next RowCount
NextColCount