Hi all,

I have a array defined

Public Type fld_cis_mng
mng_no_o As String ' J - 10
mng_no_n As String ' K - 11
mng_id As String ' L - 12
mng_fname As String ' M - 13
mng_sname As String ' N - 14
mng_email As String ' O - 15
End Type
Public cis_mng() As fld_cis_mng

then I am loading data into this array going thru the cells and write the
values

Workbooks(fl_util).Sheets(sht_name).Activate
'Select last used cell in row
rowfree = Cells(Rows.count, 10).End(xlUp).Row
t_cis_m = 0
j = 0
ReDim cis_mng(rowfree)
For i = 2 To rowfree ' go row by row
j = j + 1
cis_mng(j).mng_no_o = Worksheets(sht_name).Cells(i, 10).text
cis_mng(j).mng_no_n = Worksheets(sht_name).Cells(i, 11).text
cis_mng(j).mng_id = Worksheets(sht_name).Cells(i, 12).text
cis_mng(j).mng_fname = Worksheets(sht_name).Cells(i, 13).text
cis_mng(j).mng_sname = Worksheets(sht_name).Cells(i, 14).text
cis_mng(j).mng_email = Worksheets(sht_name).Cells(i, 15).text
Next i
ReDim Preserve cis_mng(j)

So then I have different array like this and I would like to go thru the one
array and search via unique id the record with the correct value. I have over
6500 rown in each array and it takes long time to go thru the both array.

At the moment I am using

for i = 1 to A_max
for j = 1 to B_max
if value(i).id = value(j).id then
value(i).no = value(j).no
exit for
end if
next j
next i

It is something to not go record by record but use some find/search function?

Thanks a lot

Stan

Your searching through arrays is the quickest method. it may be slow
depending how much memeory if in your computer. but your search method can
be reduced by 1/2. You are doubling checking values.

You are checking i = 25 and j = 30 as well as j = 30 and i = 25. With a
single dimension array it is the same check.

Use this method
A_Max will always = B_Max

for i = 1 to (A_max - 1)
for j = i to B_max
if value(i).id = value(j).id then
value(i).no = value(j).no
exit for
end if
next j
next i


"Laurel" wrote:

Hi all,

I have a array defined

Public Type fld_cis_mng
mng_no_o As String ' J - 10
mng_no_n As String ' K - 11
mng_id As String ' L - 12
mng_fname As String ' M - 13
mng_sname As String ' N - 14
mng_email As String ' O - 15
End Type
Public cis_mng() As fld_cis_mng

then I am loading data into this array going thru the cells and write the
values

Workbooks(fl_util).Sheets(sht_name).Activate
'Select last used cell in row
rowfree = Cells(Rows.count, 10).End(xlUp).Row
t_cis_m = 0
j = 0
ReDim cis_mng(rowfree)
For i = 2 To rowfree ' go row by row
j = j + 1
cis_mng(j).mng_no_o = Worksheets(sht_name).Cells(i, 10).text
cis_mng(j).mng_no_n = Worksheets(sht_name).Cells(i, 11).text
cis_mng(j).mng_id = Worksheets(sht_name).Cells(i, 12).text
cis_mng(j).mng_fname = Worksheets(sht_name).Cells(i, 13).text
cis_mng(j).mng_sname = Worksheets(sht_name).Cells(i, 14).text
cis_mng(j).mng_email = Worksheets(sht_name).Cells(i, 15).text
Next i
ReDim Preserve cis_mng(j)

So then I have different array like this and I would like to go thru the one
array and search via unique id the record with the correct value. I have over
6500 rown in each array and it takes long time to go thru the both array.

At the moment I am using

for i = 1 to A_max
for j = 1 to B_max
if value(i).id = value(j).id then
value(i).no = value(j).no
exit for
end if
next j
next i

It is something to not go record by record but use some find/search function?

Thanks a lot

Stan

I will try to explain more...

I have two arrays.. one with personal data and next wit aditional data..
I need to update first array with data from second array..

Therefore I am going record by recod in one aray and via for cycle try to
find in another array the correct record (comparing IDs) and update the first
array.

example..

Public Type fld_person
id As String ' ID
f_name As String ' First name
s_name As String ' Last Name
mng_id As String ' manager id
mng_name As String ' manager name
End Type
Public Person_A() As fld_person
Public Person_B() As fld_person

In one cycle I am loading data form one excel sheet (User ID, F_name, S_name)
and in second cycle I am loadint data into second array (User ID, Manager
ID, Manager Name).

Then I have to go thru the arrays and via user_id find the correct user and
updaet first array for manager details.

both tables has around 6500 rows.
Computer memory dosn't help.. the same time if I have 512MB or 2GB of ram


for i = 1 to A_max) ' go thru the first array
record by record
for j = 1 to B_max ' go thru the second array
record by record
if Person_A(i).id = Person_B(j).id then
Person_A(i).mng_id = Person_B(j).mng_id
Person_A(i).mng_name = Person_B(j).mng_name
exit for
end if
next j
next i

And also load more then 1000 rows is quite slow...

thanks

"Joel" wrote:

Your searching through arrays is the quickest method. it may be slow
depending how much memeory if in your computer. but your search method can
be reduced by 1/2. You are doubling checking values.

You are checking i = 25 and j = 30 as well as j = 30 and i = 25. With a
single dimension array it is the same check.

Use this method
A_Max will always = B_Max

for i = 1 to (A_max - 1)
for j = i to B_max
if value(i).id = value(j).id then
value(i).no = value(j).no
exit for
end if
next j
next i


"Laurel" wrote:

Hi all,

I have a array defined

Public Type fld_cis_mng
mng_no_o As String ' J - 10
mng_no_n As String ' K - 11
mng_id As String ' L - 12
mng_fname As String ' M - 13
mng_sname As String ' N - 14
mng_email As String ' O - 15
End Type
Public cis_mng() As fld_cis_mng

then I am loading data into this array going thru the cells and write the
values

Workbooks(fl_util).Sheets(sht_name).Activate
'Select last used cell in row
rowfree = Cells(Rows.count, 10).End(xlUp).Row
t_cis_m = 0
j = 0
ReDim cis_mng(rowfree)
For i = 2 To rowfree ' go row by row
j = j + 1
cis_mng(j).mng_no_o = Worksheets(sht_name).Cells(i, 10).text
cis_mng(j).mng_no_n = Worksheets(sht_name).Cells(i, 11).text
cis_mng(j).mng_id = Worksheets(sht_name).Cells(i, 12).text
cis_mng(j).mng_fname = Worksheets(sht_name).Cells(i, 13).text
cis_mng(j).mng_sname = Worksheets(sht_name).Cells(i, 14).text
cis_mng(j).mng_email = Worksheets(sht_name).Cells(i, 15).text
Next i
ReDim Preserve cis_mng(j)

So then I have different array like this and I would like to go thru the one
array and search via unique id the record with the correct value. I have over
6500 rown in each array and it takes long time to go thru the both array.

At the moment I am using

for i = 1 to A_max
for j = 1 to B_max
if value(i).id = value(j).id then
value(i).no = value(j).no
exit for
end if
next j
next i

It is something to not go record by record but use some find/search function?

Thanks a lot

Stan

Your code should look something like this. could figure out the sheet names
becasue you only posted part of the code. This should be a good example to
get you started

Public cis_mng As fld_cis_mng

With Workbooks(fl_util).Sheets(sht_name_EMPLOYEE)
.Activate
'Select last used cell in row
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set EMPLOYEERange = .Range(Cells(1, "J"), Cells(LastRow, "J"))
End With
With Workbooks(fl_util).Sheets(sht_name_MANAGER)
.Activate
'Select last used cell in row
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set MANAGERRANGE = .Range(Cells(1, "J"), Cells(LastRow, "J"))
End With

t_cis_m = 0
j = 0

For Each cell In MANAGERRANGE
Set c = EMPLOYEERange.Find(what:=cell.Value, LookIn:=xlValues)
If Not c Is Nothing Then
With Worksheets(sht_MANAGER)
cis_mng.mng_no_o = .Cells(cell.Row, "J").Text
cis_mng.mng_no_n = .Cells(cell.Row, "K").Text
cis_mng.mng_id = .Cells(cell.Row, "L").Text
cis_mng.mng_fname = .Cells(cell.Row, "M").Text
cis_mng.mng_sname = .Cells(cell.Row, "N").Text
cis_mng.mng_email = .Cells(cell.Row, "O").Text
End With
End If
Next cell

End Sub

"Laurel" wrote:

I will try to explain more...

I have two arrays.. one with personal data and next wit aditional data..
I need to update first array with data from second array..

Therefore I am going record by recod in one aray and via for cycle try to
find in another array the correct record (comparing IDs) and update the first
array.

example..

Public Type fld_person
id As String ' ID
f_name As String ' First name
s_name As String ' Last Name
mng_id As String ' manager id
mng_name As String ' manager name
End Type
Public Person_A() As fld_person
Public Person_B() As fld_person

In one cycle I am loading data form one excel sheet (User ID, F_name, S_name)
and in second cycle I am loadint data into second array (User ID, Manager
ID, Manager Name).

Then I have to go thru the arrays and via user_id find the correct user and
updaet first array for manager details.

both tables has around 6500 rows.
Computer memory dosn't help.. the same time if I have 512MB or 2GB of ram


for i = 1 to A_max) ' go thru the first array
record by record
for j = 1 to B_max ' go thru the second array
record by record
if Person_A(i).id = Person_B(j).id then
Person_A(i).mng_id = Person_B(j).mng_id
Person_A(i).mng_name = Person_B(j).mng_name
exit for
end if
next j
next i

And also load more then 1000 rows is quite slow...

thanks

"Joel" wrote:

Your searching through arrays is the quickest method. it may be slow
depending how much memeory if in your computer. but your search method can
be reduced by 1/2. You are doubling checking values.

You are checking i = 25 and j = 30 as well as j = 30 and i = 25. With a
single dimension array it is the same check.

Use this method
A_Max will always = B_Max

for i = 1 to (A_max - 1)
for j = i to B_max
if value(i).id = value(j).id then
value(i).no = value(j).no
exit for
end if
next j
next i


"Laurel" wrote:

Hi all,

I have a array defined

Public Type fld_cis_mng
mng_no_o As String ' J - 10
mng_no_n As String ' K - 11
mng_id As String ' L - 12
mng_fname As String ' M - 13
mng_sname As String ' N - 14
mng_email As String ' O - 15
End Type
Public cis_mng() As fld_cis_mng

then I am loading data into this array going thru the cells and write the
values

Workbooks(fl_util).Sheets(sht_name).Activate
'Select last used cell in row
rowfree = Cells(Rows.count, 10).End(xlUp).Row
t_cis_m = 0
j = 0
ReDim cis_mng(rowfree)
For i = 2 To rowfree ' go row by row
j = j + 1
cis_mng(j).mng_no_o = Worksheets(sht_name).Cells(i, 10).text
cis_mng(j).mng_no_n = Worksheets(sht_name).Cells(i, 11).text
cis_mng(j).mng_id = Worksheets(sht_name).Cells(i, 12).text
cis_mng(j).mng_fname = Worksheets(sht_name).Cells(i, 13).text
cis_mng(j).mng_sname = Worksheets(sht_name).Cells(i, 14).text
cis_mng(j).mng_email = Worksheets(sht_name).Cells(i, 15).text
Next i
ReDim Preserve cis_mng(j)

So then I have different array like this and I would like to go thru the one
array and search via unique id the record with the correct value. I have over
6500 rown in each array and it takes long time to go thru the both array.

At the moment I am using

for i = 1 to A_max
for j = 1 to B_max
if value(i).id = value(j).id then
value(i).no = value(j).no
exit for
end if
next j
next i

It is something to not go record by record but use some find/search function?

Thanks a lot

Stan

Thank you so much.. I will try to use today evening.
But I have another issue..

As I said I have some data sheets.
Be accurate I have 3 data sheets
1/ table row 6500 col 105
2/ table row 2000 col 60
3/ table row 1600 col 60

and only unique idetifier is employee ID..

So I am doing something like array with subarrays. Array with managers and
subarray with employees below each manager.

Then I am checking something like status of employee, manager etc...

there fore is for me great to have big array withl all information and when
I fill array I can very easy unload all data regarding some conditions...

But I see that id the count of record growin up to 1200 records, the loading
into memory is slower and slower...

there fore I am trying to find solution to speedup this load, update
procedures...

Do you have any idea?



"Joel" wrote:

Your code should look something like this. could figure out the sheet names
becasue you only posted part of the code. This should be a good example to
get you started

Public cis_mng As fld_cis_mng

With Workbooks(fl_util).Sheets(sht_name_EMPLOYEE)
.Activate
'Select last used cell in row
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set EMPLOYEERange = .Range(Cells(1, "J"), Cells(LastRow, "J"))
End With
With Workbooks(fl_util).Sheets(sht_name_MANAGER)
.Activate
'Select last used cell in row
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set MANAGERRANGE = .Range(Cells(1, "J"), Cells(LastRow, "J"))
End With

t_cis_m = 0
j = 0

For Each cell In MANAGERRANGE
Set c = EMPLOYEERange.Find(what:=cell.Value, LookIn:=xlValues)
If Not c Is Nothing Then
With Worksheets(sht_MANAGER)
cis_mng.mng_no_o = .Cells(cell.Row, "J").Text
cis_mng.mng_no_n = .Cells(cell.Row, "K").Text
cis_mng.mng_id = .Cells(cell.Row, "L").Text
cis_mng.mng_fname = .Cells(cell.Row, "M").Text
cis_mng.mng_sname = .Cells(cell.Row, "N").Text
cis_mng.mng_email = .Cells(cell.Row, "O").Text
End With
End If
Next cell

End Sub

"Laurel" wrote:

I don't know why you are trying to put the worksheet data into arrays.
Copying data into arrays is just eating up memory and slowing down the
program. You already havve the data in the workbook, why create a 2nd copy.
Everything can be done directly from the worksheet.

I don't know how you are unloading the data. Data can be exported a lot of
diffferent ways. You don't have to do it from an arrray. Can you give more
info on your unloading process.

"Laurel" wrote:

Thank you so much.. I will try to use today evening.
But I have another issue..

As I said I have some data sheets.
Be accurate I have 3 data sheets
1/ table row 6500 col 105
2/ table row 2000 col 60
3/ table row 1600 col 60

and only unique idetifier is employee ID..

So I am doing something like array with subarrays. Array with managers and
subarray with employees below each manager.

Then I am checking something like status of employee, manager etc...

there fore is for me great to have big array withl all information and when
I fill array I can very easy unload all data regarding some conditions...

But I see that id the count of record growin up to 1200 records, the loading
into memory is slower and slower...

there fore I am trying to find solution to speedup this load, update
procedures...

Do you have any idea?



"Joel" wrote:

Your code should look something like this. could figure out the sheet names
becasue you only posted part of the code. This should be a good example to
get you started

Public cis_mng As fld_cis_mng

With Workbooks(fl_util).Sheets(sht_name_EMPLOYEE)
.Activate
'Select last used cell in row
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set EMPLOYEERange = .Range(Cells(1, "J"), Cells(LastRow, "J"))
End With
With Workbooks(fl_util).Sheets(sht_name_MANAGER)
.Activate
'Select last used cell in row
LastRow = Cells(Rows.Count, "J").End(xlUp).Row
Set MANAGERRANGE = .Range(Cells(1, "J"), Cells(LastRow, "J"))
End With

t_cis_m = 0
j = 0

For Each cell In MANAGERRANGE
Set c = EMPLOYEERange.Find(what:=cell.Value, LookIn:=xlValues)
If Not c Is Nothing Then
With Worksheets(sht_MANAGER)
cis_mng.mng_no_o = .Cells(cell.Row, "J").Text
cis_mng.mng_no_n = .Cells(cell.Row, "K").Text
cis_mng.mng_id = .Cells(cell.Row, "L").Text
cis_mng.mng_fname = .Cells(cell.Row, "M").Text
cis_mng.mng_sname = .Cells(cell.Row, "N").Text
cis_mng.mng_email = .Cells(cell.Row, "O").Text
End With
End If
Next cell

End Sub

"Laurel" wrote: