View RSS Feed


Excel Macro to Populate a Range based on Info in Another Area of Workbook

Rate this Entry
by , 5th May 2012 at 01:46 AM (3335 Views)
Here is the input:
Activity, start week, end week

Based on the above info in the the first three columns of a spreadsheet, put a number or a string in the same row under the appropriate week number. So if an activity happens from week 18 (this week) to week 25, the corresponding columns would be filled with the number 1. So far I have not been able to figure out how to do this using macros. Seems pretty simple but I'm struggling since I have zero experience with Visual Basic and objects, methods, prodecures,etc.

I can get the desired result using Excel by putting the followiing statement in every column .... =IF(AND(week<=start,week>=stop), 1,""), where week, start, stop are defined ranges.
I tried to do the macro record but that doesn't yield a useful stating point


  1. CAM's Avatar
    Let me clarify something, is this what the sheet looks like?

    A=Activity | B=Start | C=End | D=Week 1 | E=Week 2| F=Week 3 | G=Week 4 | etc.
    Project 1 | 2 | 4 | (blank) | 1 | 1 | 1 |

    And you want 1's in the relative week column depending on the input in start and end?


Total Trackbacks 0
Trackback URL: