| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search based on comparing several values in the same row and column
Hello All
I need advice on the following problem: i need a code which checks each cell in a range for the values located in the same row and column (headings) and based on the findings looks for the cell with the same row/column values in another workbook. If it finds such a cell, then it has to copy formula located in this cell. The problem is that for each cell i have multiple headings (both in row and in column), some of them are located in merged cells, so sometimes i have to look for the value not in the same column, but several columns earlier. Additional difficulty is that i don't always know how many columns earlier i have to look. In other words, for example, i have a heading with months name, which can be merged over several cells, next row i have 2 or 3 parameters (columns) for each month, and it can be that in a next row i have 2 more subparameters for each parameter. So the table may look like this: | January | Value Amount ______________| SubPar1 SubPar2 SubPar1 SubPar2 Catagory | Item1 | Item2 | For the time being the solution i came with is to create additional row and column which keeps concatenated values for each row/ column. Like this: Category/Item1 And January/Value/SubPar1 The problem is that i have to create this row and column manually, which is very ineffective. I'm sure there must be better solution, but cannot produce something workable. My present code goes like this: ColC and RowC are row and column which i create manually MonthArray keeps the names of the month that are relevant wbn - new workbook wbo - old workbook sn - sheet name cLastRow1 = Workbooks(wbn).Worksheets(sn).Cells(Rows.Count, ColC).End(xlUp).Row cLastRow2 = wbo.Worksheets(sn).Cells(Rows.Count, ColC).End(xlUp).Row cLastCol1 = Workbooks(wbn).Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column cLastCol2 = wbo.Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column For i1 = 1 To cLastCol1 For i2 = 1 To cLastCol2 cCodeN = Workbooks(wbn).Worksheets(sn).Cells(RowC, i1).Value cCodeO = wbo.Worksheets(sn).Cells(RowC, i2).Value For k = 1 To UBound(MonthArray) If cCodeN = cCodeO And cCodeN < "" And InStr(1, cCodeN, MonthArray(k)) 0 Then For j1 = 1 To cLastRow1 For j2 = 1 To cLastRow2 rCodeN = Workbooks(wbn).Worksheets(sn).Cells(j1, ColC).Value rCodeO = wbo.Worksheets(sn).Cells(j2, ColC).Value If rCodeN = rCodeO And rCodeN < "" Then Workbooks(wbn).Worksheets(sn).Cells(j1, i1).Formula = wbo.Worksheets(sn).Cells(j2, i2).Formula End If Next j2 Next j1 End If Next k Next i2 Next i1 End Sub Another question is if there is a way to improve this code, cause it's quite slow for a big range. I've heard that usually it's more efficient to use find, instead of cycling over the whole range cell by cell, but cannot figure out how to use it here. Any help will be much appriciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search based on comparing several values in the same row and column
I can help for making the code faster...
Somewhere at the beginning of the code add this line: Application.Calculation=xlManual Then just before the end of the code add: Application.Calculation=xlAutomatic You'll see that your code will fly :-) -----Original Message----- Hello All I need advice on the following problem: i need a code which checks each cell in a range for the values located in the same row and column (headings) and based on the findings looks for the cell with the same row/column values in another workbook. If it finds such a cell, then it has to copy formula located in this cell. The problem is that for each cell i have multiple headings (both in row and in column), some of them are located in merged cells, so sometimes i have to look for the value not in the same column, but several columns earlier. Additional difficulty is that i don't always know how many columns earlier i have to look. In other words, for example, i have a heading with months name, which can be merged over several cells, next row i have 2 or 3 parameters (columns) for each month, and it can be that in a next row i have 2 more subparameters for each parameter. So the table may look like this: | January | Value Amount ______________| SubPar1 SubPar2 SubPar1 SubPar2 Catagory | Item1 | Item2 | For the time being the solution i came with is to create additional row and column which keeps concatenated values for each row/ column. Like this: Category/Item1 And January/Value/SubPar1 The problem is that i have to create this row and column manually, which is very ineffective. I'm sure there must be better solution, but cannot produce something workable. My present code goes like this: ColC and RowC are row and column which i create manually MonthArray keeps the names of the month that are relevant wbn - new workbook wbo - old workbook sn - sheet name cLastRow1 = Workbooks(wbn).Worksheets(sn).Cells (Rows.Count, ColC).End(xlUp).Row cLastRow2 = wbo.Worksheets(sn).Cells(Rows.Count, ColC).End (xlUp).Row cLastCol1 = Workbooks(wbn).Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column cLastCol2 = wbo.Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column For i1 = 1 To cLastCol1 For i2 = 1 To cLastCol2 cCodeN = Workbooks(wbn).Worksheets(sn).Cells(RowC, i1).Value cCodeO = wbo.Worksheets(sn).Cells(RowC, i2).Value For k = 1 To UBound(MonthArray) If cCodeN = cCodeO And cCodeN < "" And InStr (1, cCodeN, MonthArray(k)) 0 Then For j1 = 1 To cLastRow1 For j2 = 1 To cLastRow2 rCodeN = Workbooks(wbn).Worksheets(sn).Cells(j1, ColC).Value rCodeO = wbo.Worksheets(sn).Cells (j2, ColC).Value If rCodeN = rCodeO And rCodeN < "" Then Workbooks(wbn).Worksheets (sn).Cells(j1, i1).Formula = wbo.Worksheets(sn).Cells(j2, i2).Formula End If Next j2 Next j1 End If Next k Next i2 Next i1 End Sub Another question is if there is a way to improve this code, cause it's quite slow for a big range. I've heard that usually it's more efficient to use find, instead of cycling over the whole range cell by cell, but cannot figure out how to use it here. Any help will be much appriciated. . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search based on comparing several values in the same row and column
thanks for your advice. i'll try it.
Anyone, any other suggestions concerning first problem? wrote in message ... I can help for making the code faster... Somewhere at the beginning of the code add this line: Application.Calculation=xlManual Then just before the end of the code add: Application.Calculation=xlAutomatic You'll see that your code will fly :-) -----Original Message----- Hello All I need advice on the following problem: i need a code which checks each cell in a range for the values located in the same row and column (headings) and based on the findings looks for the cell with the same row/column values in another workbook. If it finds such a cell, then it has to copy formula located in this cell. The problem is that for each cell i have multiple headings (both in row and in column), some of them are located in merged cells, so sometimes i have to look for the value not in the same column, but several columns earlier. Additional difficulty is that i don't always know how many columns earlier i have to look. In other words, for example, i have a heading with months name, which can be merged over several cells, next row i have 2 or 3 parameters (columns) for each month, and it can be that in a next row i have 2 more subparameters for each parameter. So the table may look like this: | January | Value Amount ______________| SubPar1 SubPar2 SubPar1 SubPar2 Catagory | Item1 | Item2 | For the time being the solution i came with is to create additional row and column which keeps concatenated values for each row/ column. Like this: Category/Item1 And January/Value/SubPar1 The problem is that i have to create this row and column manually, which is very ineffective. I'm sure there must be better solution, but cannot produce something workable. My present code goes like this: ColC and RowC are row and column which i create manually MonthArray keeps the names of the month that are relevant wbn - new workbook wbo - old workbook sn - sheet name cLastRow1 = Workbooks(wbn).Worksheets(sn).Cells (Rows.Count, ColC).End(xlUp).Row cLastRow2 = wbo.Worksheets(sn).Cells(Rows.Count, ColC).End (xlUp).Row cLastCol1 = Workbooks(wbn).Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column cLastCol2 = wbo.Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column For i1 = 1 To cLastCol1 For i2 = 1 To cLastCol2 cCodeN = Workbooks(wbn).Worksheets(sn).Cells(RowC, i1).Value cCodeO = wbo.Worksheets(sn).Cells(RowC, i2).Value For k = 1 To UBound(MonthArray) If cCodeN = cCodeO And cCodeN < "" And InStr (1, cCodeN, MonthArray(k)) 0 Then For j1 = 1 To cLastRow1 For j2 = 1 To cLastRow2 rCodeN = Workbooks(wbn).Worksheets(sn).Cells(j1, ColC).Value rCodeO = wbo.Worksheets(sn).Cells (j2, ColC).Value If rCodeN = rCodeO And rCodeN < "" Then Workbooks(wbn).Worksheets (sn).Cells(j1, i1).Formula = wbo.Worksheets(sn).Cells(j2, i2).Formula End If Next j2 Next j1 End If Next k Next i2 Next i1 End Sub Another question is if there is a way to improve this code, cause it's quite slow for a big range. I've heard that usually it's more efficient to use find, instead of cycling over the whole range cell by cell, but cannot figure out how to use it here. Any help will be much appriciated. . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search based on comparing several values in the same row and column
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search based on comparing several values in the same row and column
the other common one is to use:
Application.screenupdating = false ' your code Application.screenupdating = rue For Each loops are supposed to be a lot faster than For Next, so instead of: For i1 = 1 To cLastCol1 You could try For Each r1 In MyColumn where r1 is a Range (a single cell), and MyColumn is also a Range (your columns) You had: cLastCol1 = Workbooks(wbn).Worksheets(sn).Cells(RowC,Columns.C ount).End(xlToLeft).Column this would be replaced by (untested!): Set myColumn = Workbooks(wbn).Worksheets(sn).Range(RowC,1).resize (1,columns.count) then the code: cCodeN = Workbooks(wbn).Worksheets(sn).Cells(RowC,i1).Value would be replaced by: cCodeN = r1.Value Since r1 is the current cell in the loop. ------ You also use Workbooks(wbn).Worksheets(sn) wbo.Worksheets(sn) Assuming they are different, I'd set up variables: Dim SourceSheet as worksheet Dim DestSheet as worksheet Set SourceSheet = Workbooks(wbn).Worksheets(sn) Set DestSheet = wbo.Worksheets(sn) Saves a lot of typing, and is easier to read. Darren "Vlad" wrote in message om... (Vlad) wrote in message . com... thanks for your advice. i'll try it. Anyone, any other suggestions concerning first problem? I tried your advice, and didn't see any effect. I have to copy something like 15000 links from one file to another and it takes about 1/2 hour on pentium 4, which is really unacceptable, considering that it's only part of the code... wrote in message ... I can help for making the code faster... Somewhere at the beginning of the code add this line: Application.Calculation=xlManual Then just before the end of the code add: Application.Calculation=xlAutomatic You'll see that your code will fly :-) -----Original Message----- Hello All I need advice on the following problem: i need a code which checks each cell in a range for the values located in the same row and column (headings) and based on the findings looks for the cell with the same row/column values in another workbook. If it finds such a cell, then it has to copy formula located in this cell. The problem is that for each cell i have multiple headings (both in row and in column), some of them are located in merged cells, so sometimes i have to look for the value not in the same column, but several columns earlier. Additional difficulty is that i don't always know how many columns earlier i have to look. In other words, for example, i have a heading with months name, which can be merged over several cells, next row i have 2 or 3 parameters (columns) for each month, and it can be that in a next row i have 2 more subparameters for each parameter. So the table may look like this: | January | Value Amount ______________| SubPar1 SubPar2 SubPar1 SubPar2 Catagory | Item1 | Item2 | For the time being the solution i came with is to create additional row and column which keeps concatenated values for each row/ column. Like this: Category/Item1 And January/Value/SubPar1 The problem is that i have to create this row and column manually, which is very ineffective. I'm sure there must be better solution, but cannot produce something workable. My present code goes like this: ColC and RowC are row and column which i create manually MonthArray keeps the names of the month that are relevant wbn - new workbook wbo - old workbook sn - sheet name cLastRow1 = Workbooks(wbn).Worksheets(sn).Cells (Rows.Count, ColC).End(xlUp).Row cLastRow2 = wbo.Worksheets(sn).Cells(Rows.Count, ColC).End (xlUp).Row cLastCol1 = Workbooks(wbn).Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column cLastCol2 = wbo.Worksheets(sn).Cells(RowC, Columns.Count).End(xlToLeft).Column For i1 = 1 To cLastCol1 For i2 = 1 To cLastCol2 cCodeN = Workbooks(wbn).Worksheets(sn).Cells(RowC, i1).Value cCodeO = wbo.Worksheets(sn).Cells(RowC, i2).Value For k = 1 To UBound(MonthArray) If cCodeN = cCodeO And cCodeN < "" And InStr (1, cCodeN, MonthArray(k)) 0 Then For j1 = 1 To cLastRow1 For j2 = 1 To cLastRow2 rCodeN = Workbooks(wbn).Worksheets(sn).Cells(j1, ColC).Value rCodeO = wbo.Worksheets(sn).Cells (j2, ColC).Value If rCodeN = rCodeO And rCodeN < "" Then Workbooks(wbn).Worksheets (sn).Cells(j1, i1).Formula = wbo.Worksheets(sn).Cells(j2, i2).Formula End If Next j2 Next j1 End If Next k Next i2 Next i1 End Sub Another question is if there is a way to improve this code, cause it's quite slow for a big range. I've heard that usually it's more efficient to use find, instead of cycling over the whole range cell by cell, but cannot figure out how to use it here. Any help will be much appriciated. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| Find and sum values based on a column search | Excel Discussion (Misc queries) | |||
| comparing column values | Excel Worksheet Functions | |||
| Comparing cell values then labeling them based on comparision | Excel Discussion (Misc queries) | |||
| How do I search thr'o column and put unique values in differnt sheet and sum corresponding values in | Excel Programming | |||
![Vlad[_5_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
