PDA

View Full Version : [SOLVED] Set a variable based on another variable



ValerieT
08-27-2013, 12:50 AM
Hello
My Excel workbook contains a sheet per month (doing HR stuff based on hire date). In order to have only one macro for all workbook I need my calculation to be based on variable. I understand the theory but I am a beginner and don't know a better way to avoid all the "if" you can see below :


Sheet name

Representing month

1st day month value



1

01/01/2013

41275



2

01/02/2013

41306



3

01/03/2013

41334



4

01/04/2013

41365



5

01/05/2013

41395



6

01/06/2013

41426



7

01/07/2013

41456




Dim Mysheet as integer
Dim Myvalue as integer

MySheet=ActiveSheet.Name

If mysheet=1 then my value=41275
if mysheet=2 then my value=41306
etc..

THANKS!

mancubus
08-27-2013, 05:03 AM
Hi. If you're always working with "ActiveSheet" and ActiveSheet name is a number from 1 to 12...
Dim MyValue As Long MyValue = DateSerial(Year(Date), CInt(ActiveSheet.Name), 1)

ValerieT
08-29-2013, 12:48 AM
Hi. If you're always working with "ActiveSheet" and ActiveSheet name is a number from 1 to 12...
Dim MyValue As Long MyValue = DateSerial(Year(Date), CInt(ActiveSheet.Name), 1)

Thanks! I love when you can reduce to one single line and learn something new at the same time!.. I'll try that right now..

mancubus
08-29-2013, 01:53 AM
you're most welcome.i'm glad it helped.