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.