change a number by 1 value within macro

**yrndtn** · 11-03-2011, 06:28 AM

Hello,

I'm having a workbook with a lot of formulas to do some calculation and repeated actions.
every week i need to duplicate the latest spreadsheet and change value in the VLOOKUP formula. for example: i need to change this formula VLOOKUP(A2,WEEK(3)A3:G550,3,1) to this formula: VLOOKUP(A2,WEEK(4)A3:G550,3,1).
nothing in the formula is changing, beside the week number which increase by 1 each time.

If i'll find a macro to do so, it will save me a lot of work...

Thanks

**royUK** · 11-03-2011, 06:54 AM

Why not have a template workbook that you paste the data into?

**yrndtn** · 11-03-2011, 07:20 AM

This is kind of what i'm doing now, but if i'll have a macro to change the formula, it will save me much time.

**royUK** · 11-03-2011, 07:31 AM

Using a template with the formulas in shouldn't need a macro to change them

**yrndtn** · 11-03-2011, 07:53 AM

OK, i understand that i don't use it this way, probably because i'm not familiar with this...
I tries to work around with it a little bit, without any success. can you guide me on how to do it?

Thanks

**royUK** · 11-03-2011, 09:11 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

See

http://excel-it.com/excel_templates.htm

**yrndtn** · 11-03-2011, 10:20 AM

Attached a sample file.
in this file there is a sheet for each week that contains some formulas, and a cell (C30) that sum previous week total.
each week I need to duplicate the last week workbook (it always like that), and change the C30 formula to the previous week. there are more action i'm doing each time, but if i'll work this problem through, i can record macro that will save me a lot of time.

Thanks