Please help to Automate my Spreadsheet Using If conditions in Macros

[kinirobin]

Hi All,
I have a sheet with columns Amount, City and Name. I have a set of names for each location ex: North - Mr.X, Mr.Y, South - Mr. A, Mr. B, East - Mr. D, Mr.F. And a few cities in each of the locations Like North - Boston, Detroit Etc.

Can Anyone please help me to provide a if condition formula or a Macro which looks at a range of amount(<=200) , City and then give the name of the person as per the cities in a location. Ex.

Amount City Name

$200 Boston Mr. X

Many Thanks,
Kinirobin

 

[JLGWhiz]

This will filter the rows where the value in column A is <=200 and will put those items with their corresponding name and address in columns E:G beginning at row 2. the code should be copied to the standard code module 1. See bottom of post for accesss instructions.

Sub getNameaddr()
Dim sh As Worksheet, lr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
sh.Range("A1:C" & lr).AutoFilter 1, "<=200"
sh.Range("A2:C" & lr).SpecialCells(xlCellTypeVisible).Copy sh.Range("E2")
sh.UsedRange.AutoFilter
End Sub

 

[kinirobin]

Thanks for the quick response JLGWhiz, My question was little different, may be I have not put across my point correctly the first time.

Each Region has a list of cities in it for Example - North = Boston, Detroit. South = Dallas, Florida. West = California, Memphis

So imagine if 450 is entered in Column A and Boston is entered in Column B then the Column C should auto calculate and show - Mr. A

Here is the example:


[TABLE="width: 801"]
<tbody>[TR]
[TD]Range[/TD]
[TD] E.g data[/TD]
[TD]North[/TD]
[TD]South[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]500 < =[/TD]
[TD] 450[/TD]
[TD]Mr. A[/TD]
[TD]Mr. A[/TD]
[TD]Mr. A[/TD]
[/TR]
[TR]
[TD]501-4999[/TD]
[TD] 550[/TD]
[TD]Mr. A, Mr. B, Mr. C[/TD]
[TD]Mr. A, Mr. B[/TD]
[TD]Mr. B, Mr. C[/TD]
[/TR]
[TR]
[TD]5000-24999[/TD]
[TD] 5,500[/TD]
[TD]Mr.X, Mr.Y[/TD]
[TD]Mr. D, Mr. O[/TD]
[TD]MR. L[/TD]
[/TR]
[TR]
[TD]25000-49999[/TD]
[TD] 25,500[/TD]
[TD]Mr. M, Mr, G[/TD]
[TD]Mr. B, Mr. A[/TD]
[TD]Mr. W, Mr. I[/TD]
[/TR]
[TR]
[TD]50000 >[/TD]
[TD] 55,000[/TD]
[TD]Mr. E, Mr. F[/TD]
[TD]Mr. J, Mr. S[/TD]
[TD]Mr. Z, Mr. N[/TD]
[/TR]
</tbody>[/TABLE]
Hope This helps.

Many thanks,

Kinirobin