I need to search for a cell value in another sheet and copy the parent value of the cell which it reports
into. Below are the steps I am following but not able to put in in macro as I am new to writting macro and getting stuck at
Request you to go through the following steps and kindly help me resolving the same.
attaching two excel sheet which I have mentioned in my Steps and the same steps are mentioned in the excel sheet called "Copy
Step 1 Select value of cell B2 in worksheet "Split Data" which is in workbook "Copy of Extract"
Step 2 Check if value of cell B2 is present in column A of 1st worksheet "Bat" which is in workbook "Copy of Mapping
Step 3 If value of cell B2 is present in column A of 1st worksheet "Bat" then start searching for the
first 1 (number 1) in column C in upward direction
NOTE: (For E.g. If B2 i.e. NNN is present on row no. 15, start searching column c from row 15(from Cell C15 to upward
direction) to upward direction and give me the value of cell A where first 1 is present "In this case it will be at row 4 and
value returned will be CCC)
Step 4 Copy the value of cell A where first "1" is present
NOTE:In this case: Copy the value "CCC"
Step 5 Copy the value of cell A in cell B2 of worksheet "Data"
NOTE:This cell is marked in RED
Step 6 if value of cell B2 is not present in column A of 1st worksheet "Bat" then
if value of cell B2 is present in column A of 2nd worksheet "Ball"
Step 7 If value of cell B2 is present in column
A of 2nd worksheet "Ball" then start searching for the first 1 (number 1) in column C in upward direction (and if Value of B2
does not exist in sheet "Ball" search it in 3rd sheet "Stump" and if not present then search it in 4th sheet "Pad")
NOTE;If Value is not present in column A of 1st sheet, Check in column A of 2nd sheet, if still not present then, Check in
column A of 3rd sheet and so on till the last sheet is present and till value is not found. If value is not found in any of
the sheets give "NA" in the cell in sheet "Data" as an output for the cell.
Step 8 Follow Step 4
Follow Step 5
Step 10 Now go to cell C2 of worksheet "Split Data"
NOTE: We will check the cells horizontally one by one till the "Sr Nos" are present in column A of workbook "Copy of Extract"
and once the Sr No ends macro will stop/end
Step 11 Check if cell C2 has any value
Step 12 If value is
present follow Step1, 2, 3, 4, 5 to copy the value of cell A in cell C2 of sheet "Data" from sheet 1, 2, 3 or 4 which are in
workbook "Copy of Mapping Details"
Step 13 If cell C2 is empty jump to cell B3
Step 14 Follow Step
1,2,3,4,5 for value of cell B3 and copy the value of cell A in cell B3 of sheet "Data" as mentioned above in step 5
Step 15 Then go to C3, follow steps 1,2,3,4,5 and then for D3 and so on till the cell
beside last cell is empty
Step 16 Continue this search of cells of sheet "Split Data" till last row. In this case
it will be row 11,Cell G11
NOTE: Please see that rows and columns will not be limited upto 11 rows or 10 columns. There can be 1000's of rows and colmns
for which we need to follow the search and copy process. The row nos. and column nos. can change based on the data available.
So, I need a macro which can be used for any no. of row and columns
I humbly hope to receive a great solution as
soon as possible.