macro to put differnet values into a cell depending on the othere cell

[tonylpcs@yahoo.co.uk]

Hi Everyone,

I need a macro that i can use to input a value into a cell depending on what the other cells value is:

when i run it i need it to say:

if cell A1 = "2" then input cell F5 with "hello" however if cell A1 = "yes" then enter in cell F5 "goodbye" but if cell A1 = "wet" then enter in cell F5 "sold by tony"

can anyone give me the code for this please?

thanks

Tony

 

[taurean]

Try this untested:

Public Sub tonyswish()
Select Case Range("A1").Value
Case 2
Range("F5").Value = "hello"
Case "yes"
Range("F5").Value = "goodbye"
Case "wet"
Range("F5").Value = "sold by tony"
Case Else
Range("F5").Value = "Not defined by tony!"
End Select
End Sub

 

[tonylpcs@yahoo.co.uk]

brilliant!

and exactly what i needed

thank you so much

Tony

 

[taurean]

On Second thoughts, you can create a lookup table in Excel which will prove to be dynamic and flexible like:

Excel Workbook
	A	B	C	D	E	F
1	2	INPUT	RESPONSE
2		2	hello
3		yes	goodbye
4		wet	sold by tony
5						hello
Sheet1

If you have Excel 2007 & above you can use this formula:

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F5</TD><TD>=IFERROR(VLOOKUP(A1,B2:C4,2,0),"not defined by tony!")</TD></TR></TABLE></TD></TR></TABLE>



In Excel 2003 and below:
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F5</TD><TD>=IF(ISERROR(VLOOKUP(A1,B2:C4,2,0)),"not defined by tony!",VLOOKUP(A1,B2:C4,2,0))</TD></TR></TABLE></TD></TR></TABLE>