# Help on a macro for excel

Discussion in 'Business Applications' started by ZWard, Nov 4, 2011.

Not open for further replies.

Joined:
Sep 28, 2011
Messages:
36
I need some help designing a macro to read from one column of ages, then put a group of Characters in another:
Here would be the structure:
IF(K# > =65)
THAN
D# = "65 and Older"
ELSE
IF(K#<=64 && K#>=55)
THAN
D# = "55-64"
ELSE
IF(K#<=54 && K#>=45)
THAN
D#="45-54"
ELSE
IF(K#<=44 && K#>=35)
THAN
D#="35-44"
ELSE
IF(K#<=34 && K#>=25)
THAN
D#="25-34"
ELSE
IF(K#<25)
THAN
D# = "Younger than 25"

Can someone write this up because I have tried numerous times to write this.

Joined:
Mar 27, 2008
Messages:
6,608
First Name:
Hans
THAN is incorrect syntax!!! ... and I imagine this is the macro code?
Corrected (not tested just out of my head)
Code:
```IF K# > =65 THEN
D# = "65 and Older"
ELSEIF K#<=64 AND  K#>=55 THEN
D# = "55-64"
ELSEIF K#<=54 and K#>=45 THEN
D#="45-54"
ELSEIF K#<=44 AND K#>=35 THEN
D#="35-44"
ELSEIF K#<=34 AND K#>=25 THEN
D#="25-34"
ELSEIF K#<25 THEN
D# = "Younger than 25"
END IF
```

3. ### Rollin_Again

Joined:
Sep 4, 2003
Messages:
4,912
You are much better off using a SELECT CASE statement instead of all those IF statements. See example below.

Code:
```Select Case k#

Case Is >= 65
D# = "65 and Older"

Case 55 To 64
D# = "55-64"

Case 45 To 54
D# = "45-54"

Case 35 To 44
D# = "35-44"

Case 25 To 34
D# = "25-34"

Case Is < 25
D# = "Younger than 25"

End Select```
Rollin

Joined:
Mar 27, 2008
Messages:
6,608
First Name:
Hans
True, was my thougth after I posted, but it was bedtime
Back 22 Nov

5. ### DaveG53

Joined:
Nov 7, 2011
Messages:
2
I believe this may help. The following formula is based on cell A1 having an AGE entered. It also requires an AGE to be entered before returning an answer. A blank cell would not provide a response. The formula is: IF(A1>=65,"65 and Older",IF(AND(65>A1,A1>54),"55 to 64",IF(AND(55>A1,A1>44),"45 to 54",IF(AND(45>A1,A1>34),"35 to 44",IF(AND(35>A1,A1>24),"25 to 35",IF(AND(25>A1,A1>0),"Under 25",""))))))

Joined:
Sep 28, 2011
Messages:
36

First to all:
I know THAN is improper it was called Psuedocode
Second to Rollin
The debugger keeps spitting out bad code on "D#" on all.
Actually its anywhere there is a #. Also they are supposed to represent cells. I don't think that's proper format for a cell call.

7. ### Rollin_Again

Joined:
Sep 4, 2003
Messages:
4,912
We need to see the code in its entirety.

Rollin

Joined:
Sep 28, 2011
Messages:
36
Well you see here's the thing, the code became erroneous that I deleted it....
All I had left was that psuedocode which is just a structure layout, essentially a flowchart laying out the very basics of code.

As Seen On