How to address copy destination cell in VBA if cell adress is written as a text elswhere

Hello!

here is a tricky one

I have a list of 4 sums (sheet2) and 4 destination cell numbers that these sums should go into stated as a text just below sums (destination is in sheet1). Example:

A B C D E
100 200 300 400 500
C81 C82 C83 C84 C85

I would like to make a command button that would transfer value 100 from sheet2 cell A1 to sheet1 cell C81.
I would use such code:

Please Login or Register  to view this content.

.....


but how should I get this X,X destination address?

adress line is generated as different argument (formula based, like "=a&b") from two different cells and is user dependent. lets say user can select G1 to be "class1" and H1 to be "room1" from a drop down list and then vlookup searches "class1" from sheet1 and states that it is in line 55, and "room1" is dedicated to be in column "D" in sheet1, so whenI use & function i get destination cell D55 for certain sum and certain selection of arguments. Next sum would get another location, like T89.

quite confusing, but maybe someone has an idea

Thanks!

hi flyman, option regarding moving data to Sheet1

Please Login or Register  to view this content.

or it can be simple transpose:

Please Login or Register  to view this content.

VBA gives an error
"Run time error 1004, application-defined or object defined error" that spots to

Please Login or Register  to view this content.

main thing is that target cells are a2:e2 in sheet2, while destination cells are stated in a3:e3 in sheet2, but they are not direct a3:e3 cells...

lets make it more simple:

A1: 100
B1: D10

how to transfer value 100 from A1 in sheet2 into dedicated cell "D10" that is stated in B1, but into "sheet1!D10" ?

well, it took longer time than i thought to figure out, but i have script working. Maybe it will be usefull for anyone else:

Please Login or Register  to view this content.

when data on the sheet2 looks like this (range on sheeet2 = e5:h6)
100 0 0 1080
J6 K6 L6 M6

script takes sheeet2!E5 value 100 ant copies it to sheet1!J6 and so on.

Hello flyman,

Please make use of the Code Tags when posting your code. This makes it easy to copy and paste the code when testing and developing solutions.