tommy1234
04-19-2015, 01:51 AM
Hello
I have an excel workbook with 2 sheets :
1. 'Data' - contain a large amount of data - items (column 'A') and their prices (column 'B').
2. 'Data2' - contain data - items (column 'A', the number of rows can be change) and their prices (column 'D', but its dynamic - some times it can be column 'C' and sometimes it can be 'E', 'F' or something else).
* there is another problem - if column 'B' in sheet 'Data' has values and there is data in sheet 'Data2' the value in column 'B' will be the value from sheet 'Data2'.
* if there is value in column 'B' in sheet 'Data' and there is no value in sheet 'Data2' then the value will be the original value
* if the field is blank and there is no value in sheet 'Data2' then it should be kept blank.
i wrote the formula i want to use, but i couldn't succeed to translate it to vba for the dynamic range (row and columns)
the formula (it works) : =IF(AND(ISERROR(VLOOKUP(A2,Data2!$A$1:$D$3,3,0))=TRUE,B2<>"")=TRUE,S2,IF(AND(ISERROR(VLOOKUP(A2,Data2!$A$1:$D$3,3,0))=TRUE,B2="")=TRUE,"",VLOOKUP(A2,Data2!$A$1:$D$3,3,0)))
thank u
I have an excel workbook with 2 sheets :
1. 'Data' - contain a large amount of data - items (column 'A') and their prices (column 'B').
2. 'Data2' - contain data - items (column 'A', the number of rows can be change) and their prices (column 'D', but its dynamic - some times it can be column 'C' and sometimes it can be 'E', 'F' or something else).
* there is another problem - if column 'B' in sheet 'Data' has values and there is data in sheet 'Data2' the value in column 'B' will be the value from sheet 'Data2'.
* if there is value in column 'B' in sheet 'Data' and there is no value in sheet 'Data2' then the value will be the original value
* if the field is blank and there is no value in sheet 'Data2' then it should be kept blank.
i wrote the formula i want to use, but i couldn't succeed to translate it to vba for the dynamic range (row and columns)
the formula (it works) : =IF(AND(ISERROR(VLOOKUP(A2,Data2!$A$1:$D$3,3,0))=TRUE,B2<>"")=TRUE,S2,IF(AND(ISERROR(VLOOKUP(A2,Data2!$A$1:$D$3,3,0))=TRUE,B2="")=TRUE,"",VLOOKUP(A2,Data2!$A$1:$D$3,3,0)))
thank u