macro using select condition

August 31, 2010 at 05:05:23
Specs: excel
I need write a macro with condtions.Here is the explanation:

i have one excel file with number of sheets.
sheet1:
Column A column B column c
ph nec tlk_21
pb sun mng_09
ph oracle odf_67
pb avaya ipg_65


sheet2:
nec adres

sheet3
sun adrs

sheet4
oracle adrs

sheet5
avaya adres

now depending on column A and B it shoudh fetch the corresponding adrs and print in column D-sheet1, i.e if it is ph and nec, it should go to sheet 2 and fetch the nec adrs, if it is pb and sun then fetch the sun adrs etc

Kindly provide a macro for this.

Waiting for your reply,
Regards,
Ranjitha

See More: macro using select condition

Report •


#1
September 1, 2010 at 21:16:24
Hi,
Require a little more information...
What are the sheet names?
What are the contents of the Sheet2 onwards column based?

This can simply be done by using formula's in the excel sheet or using IF -then- else in a macro.

Thanks.


Report •

#2
September 2, 2010 at 03:41:41
Is it really dependent on Column A? In other words why do the ph, pb, etc matter if the other sheets just contain sun, nec, etc?


Report •

#3
September 2, 2010 at 04:21:24
Hi,

I have to agree with DerbyDad03 ... from your description, the values ph and pb are not relevant.

I have three other questions:
1. Is there any logic for the location of the adrs values in worksheets: sheet1, sheet2, sheet3 and sheet4.
2. What are the cell addresses of the adrs/adres values in Sheets 1-5
(is adrs the same as adres & are these values "adrs" and "adres" or are they your shorthand for the cell contents).
3. Are the four values you have used (nec, sun, oracle, avaya) the only values you will be looking up.

Regards


Report •

Related Solutions

#4
September 2, 2010 at 21:24:24
Hi,

i need to check for ph or pb because some times ph nec will have diff address and pb nec will have diffrent address, there are still many suppliers not only these 4 , for this example i mentioned these 4 and sheet names will be like below mentioned:
sheet 2 which is containg nec adreess so name will be NEC and sheet 3 -SUN so on.
adrees will be in the form as mentioned below
sheet name- NEC
A1: Coloum name : Template for ML
mfr name
address
supplier name
ph no & so on...

A6: Coloum name: Template for MINC
mfr name
address
supplier name
ph no & so on...

so here if it is for ph nec, it should fetch the adres
"Template for ML
mfr name
address
supplier name
ph no & so on..." and paste in sheet1 colum d

if it is for pb nec, it should fetch the adrs
"Template for MINC
mfr name
address
supplier name
ph no & so on..." and paste in sheet1 colum d

so onyl i need to check 1st weather it is for PH or PB then check for supplier names.


Report •

#5
September 2, 2010 at 22:29:42
Maybe it's me, but I still don't see where ph or pb is used on any other sheet than Sheet1. We can certainly write code to look for ph and pb and then look for nec or sun, but I don't see anything in your example that tells us where to look for ph or pb.

In addition, I'll ask the same question that Humar asked earlier, just in a slightly different manner:

In your posts, you have used all of these strings various times in various locations:

address
adrees
adreess
adrs
adres

Are they all just misspellings of Address? If so, please proofread your posts before hitting submit and try to be consistent. We can't read your mind, so we don't know if any of those mean something different than another. They could be variables, or abbreviations, or whatever.


Report •

#6
September 3, 2010 at 06:19:16
Hi,

I echo the request for consistency.

Am I right in saying that your worksheets are named:
"NEC", "SUN", "ORACLE", and "AVAYA"
all of these as upper case.
Each worksheet contains contact details for the company.

The contact details appear twice on each worksheet, both in column A
The first starts with "Template for ML" in cell A1
and the second starts with "Template for MINC" in cell A6

You then say that cells A1 to A5 contain these items:
A1: Template for ML
A2: mfr name
A3: address
A4: supplier name
A5: ph no
but you then add "& so on..."
This does not make sense as you have told us that the next cell (cell A6), contains "Template for MINC"

From what you have said ph nec (you used lower case) needs to select and copy the data from a worksheet named "NEC" and the 4 cells A2, A3, A4 & A5
For pb nec it needs to select and copy the data from a worksheet named "NEC" and the 4 cells A7, A8, A9 & A10

The selected data is then copied into a worksheet named "Sheet1" and placed somewhere in column D

Four questions arise from this:
1. Is the data pasted into column D alone.
You showed us that the data on Sheet1 was like this:

A	B	C
ph	nec	tlk_21
pb	sun	mng_09
ph	oracle	odf_67
pb	avaya	ipg_65

As you didn't include row numbers, it appears that this data is in rows 1 to 4.
In which case does the copied data get concatenated into one cell in column D, alongside the appropriate 'ph/pb' & 'company name'
or is it placed in the 4 cells in columns D, E, F & G

2. Where does the source data start and is it in sequential rows. Is 'ph' in cell A1 and 'nec' in cell B1 and are 'pb' & 'sun' in cells A2 and B2
or is it that cells A1, B1 and C1 can be changed to show ph or pb in A1, a company name in B1 and some other data (not relevant to this proposed macro) in C1.

3. Are the address worksheets always named as the exact, but upper case version of the text in column B on Sheet1.

4. Is Sheet1 actually named "Sheet1"

Regards


Report •


Ask Question