Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
591 views
in Technique[技术] by (71.8m points)

VBA excel Copy Paste

enter image description here

Hi, I am totally new to Excel VBA. Firstly, I want to copy the data when the condition is met(copy data with reference to 144) Secondly, compare the cells, if it is IT Operations(Table1) to IT Operations(Table2) then copy the price(money) to column F. If the variable is no there then leave blank.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

This can be done with formulas. Here is one way of thinking about filling column F, with the prices for the matching items in column E, by matching the number given in the last row in E (144 Total); which i shall assume is E10 in this case.

Total formula in F1 which you then drag down is:

=IFERROR(IFERROR(VLOOKUP(E1,INDIRECT(CELL("address",OFFSET($H$1,MATCH(1*LEFT($E$10,FIND(" ",TRIM($E$10),1)-1),$G:$G,0)-1,,1,1))&":"&CELL("address",OFFSET($I$1,MATCH($E$10,$G:$G,0)-1,,1,1))),2,FALSE),VLOOKUP(E1,G:I,3,FALSE)),"")

In steps:

Extract the number of interest e.g. 144, and get rid of any trailing/leading whitespace using:

LEFT($E$10,FIND(" ",TRIM($E$10),1)-1)

Find which row this value is in as this will be the first row of the lookup range for this number. *1 converts text to a number.

MATCH(1*LEFT($E$10,FIND(" ",TRIM($E$10),1)-1),$G:$G,0) 

This gives row 9.

We can use something simpler to find the last row of the range, which holds 144 Total

MATCH($E$10,$G:$G,0)

This gives row 15. So we know the data lies between rows 9 and 15 for 144.

We can turn this into a range to use in a VLOOKUP with INDIRECT and OFFSET.

=CELL("address",OFFSET($G$1,MATCH(1*LEFT($E$10,FIND(" ",TRIM($E$10),1)-1),$G:$G,0)-1,,1,1))&":"&CELL("address",OFFSET($H$1,MATCH($E$10,$G:$G,0)-1,,1,1))

This gives us $G$9:$H$15. Note adjustments of -1, to put OFFSET back in the right row, and that the OFFSET start cells are in different columns to provide the columns required for the VLOOKUP.

So we can now lookup column E values e.g. Enhancement, in our newly defined range which is accessed via INDIRECT:

=VLOOKUP(E1,INDIRECT(CELL("address",OFFSET($H$1,MATCH(1*LEFT($E$10,FIND(" ",TRIM($E$10),1)-1),$G:$G,0)-1,,1,1))&":"&CELL("address",OFFSET($I$1,MATCH($E$10,$G:$G,0)-1,,1,1))),2,FALSE)

This is saying VLOOKUP(E1,$G$9:$H$15,2,FALSE) i.e. get the price column from the range for the item specified in E1.

If this is not found i.e. returns #N/A, we can use this to first check if this is because of the merged cell that holds the 144 Total; where the value is actually in column G not H, and use an IFERROR to say, if not found in $G$9:$H$15 then try for a match using columns G:I and return column 3.

Which with pseudo formula, using priorLookup as placeholder, for the formula described in the steps above, looks like:

 IFERROR(priorLookup, VLOOKUP(E1,G:I,3,FALSE))

If this still returns #N/A, we know the value is not present and we should return "". This we can handle this with another IFERROR:

 IFERROR(IFERROR(priorLookup, VLOOKUP(E1,G:I,3,FALSE)),"")

So giving us the entire formula stated at the start.

Here it is used in the sheet:

Formula in sheet


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...