Excel/Excel Macro

Advertisement


Question
QUESTION: In my excel worksheet, Column-A has numbers in all cells, in which some of them repeats.  Column-B  has corresponding numbers in some of the cells only. I need a macro to fill blank cells in Column-B with the same number in other cells against a particular number in Column-A. Eg:-

A1-1100   B1-    (Blank)
A2-1100   B2-7000
A3-1100   B3-    (Blank)
A4-1800   B4-    (Blank)
A5-1800   B5-5000

I need result in Column-B as:
B1-7000
B2-7000
B3-7000
B4-5000
B5-5000

Please help

regards
varghese

ANSWER: Hi Varghese,

Is there any correlation between the values in column A and B. The problem is that your blank values could be either before or after the current cell, that makes it difficult to devise a logic for filling up the cells.

One more question - will it ever happen that  a1 is 1100, b1 is 7000 and at the same time a2 is 1100 and b2 is 7500?

Thanks,
Gulshan.

---------- FOLLOW-UP ----------

QUESTION: Hi!
Thank you for reply.
 
There is no correlation between values in A and B.  Number in Column B is Common Number to numbers in Column A. If A1 and A2 are 1100, B1 and B2 will be 7000 only (not another number)

Hope to get an idea!

Thanks

Varghese

Answer
Hi Varghese,
The problem I'm facing while writing the macro is that A1 has no value next to 1100, when I go to A2, I find a value for 1100, but by this time, the macro has already moved on to row A2. I need to keep going forward and then backward everytime I find a value. This makes it very difficult to write a concrete logic.
Is there a possibility to arrange this data in a sorting order so that the blanks of 1100 appear after atleast one 1100 which has 7000 populated next to it.

Alternatively, when you say that 1100 will always correspond to 7000, it gives me an impression that you have a limited range of numbers in Column A which Correspond to values in Column B. If so, do you have some kind of a master list where you have the relationships defined between the Column A and B values? If we can have a ready reference list showing all the possible values in column A and their corresponding values for column B, then we can use simple Vlookup with no macro required.

Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.