Energy Star Data

[MarkRA]

Hi folks, I'm a university student interning in the real estate industry for the summer and am working with some Energy Star (ES) data as one of my assigned projects.

I'm looking to create a macro to prepare data downloaded from ES into a usable format and produce several charts, including year-over-year comparisons of energy and water consumption and greenhouse gas emissions.

The first problem I'm having is generating a region column based on a state name column. I'd like to take the ES-reported state names - which are fully spelled out as such "California" "District of Columbia (D.C.)" etc - and assign them to a particular region of my choosing (New England states + NY, NJ, PA, DE = Northeast) etc.

I was thinking about using a very long nested string function for this, but I'm sure there must be an easier way!

Thanks in advance

 

[MarkRA]

Alright - so I've successfully created the table and implemented the vlookup formula. Your code above brings up an interesting point - I want to be able to run this on whichever sheet is currently open. People will be pulling new data down from ES on a monthly basis to perform this operation.

I don't have any experience with VBA, but it looks to me like I can just eliminate the "with/end with" <with worksheets("data")=""> and <end with=""> and this will run on the currently open sheet.

Are there any recommended resources out there for learning VBA? I hate bothering you guys with such basics as what "dim lastrow" <dim lastrow="" as="" long=""> means.</dim></end></with>

 

[MarkRA]

Just came across this resource, looks like I have a bunch of reading to do!

http://www.mrexcel.com/forum/showthread.php?641953-Learning-VBA-at-a-late-age

 

[Norie]

Mark

You can't just remove the With/End With but you could replace Worksheets("Data") with ActiveSheet.