Hi all, hope everyone is ok during these times.
I started VBA literally a week ago.
Writing my first program I am pretty happy with but there is 1 section that bugs me as there must be a more efficient way to do it.
The program is the following :
As you can see, there is a lot of If statements inside one another.SelectDay: If SelectedMonth = "May" Then Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1 Else If SelectedMonth = "Jul" Then Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1 Else If SelectedMonth = "Oct" Then Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1 Else If SelectedMonth = "Dec" Then Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1 Else Countdowntoday = (SelectedDay + 31) - ActiveCell.value + 1 End If End If End If End If Return
SelectedMonth is a label defined by Left(ActiveCell.Value, 3) so that will change per run of this subroutine.
When I tried to useit comes up with a type mismatch, I tried to use Dim to define the SelectedMonth as string but that didn't seem to help. Is there another way to word this to make it more efficient? I mean this currently works but I want to improve my techniques.IF SelectedMonth = "May" OR "Jul" OR "Oct" OR "Dec"
My second problem is that at the end of my program I have a list of labels to addresses. I want to make a formulae using only the column from these addresses. For example, I want cell W1 to equal Sum(13,Column of cell 1 : 13,Column of cell 2). This is because the cells will move along my template to nearly any cell above row 13 but the calculations I do are always from the range of the starting cell to the final cell.
Below is the section of code that references these cells, as you can see i use =ActiveCell.Address to apply that current address within the label at the time.
I've been scratching my head over the second issue for a day now, and I think its currently due to my lack of knowing what options I have available to me at the current moment, rather than not knowing what I want to achieve or how I want it. I will keep trying my own methods in the meantime and will post if I find a solution. Albeit, it may be another very inefficient one.'Use December inputs 'Overtime Start Date MonthQuery = Sheets("input").Range("F17") GoSub SelectMonth GoSub DetermineWks DayQuery = Sheets("input").Range("E17") GoSub DetermineDayQueryNum GoSub SelectWeek GoSub SelectDay DecStartDate = ActiveCell.Address(0, 0) 'Overtime Finish Date MonthQuery = Sheets("input").Range("H17") GoSub SelectMonth GoSub DetermineWks DayQuery = Sheets("input").Range("G17") GoSub DetermineDayQueryNum GoSub SelectWeek GoSub SelectDay DecFinishDate = ActiveCell.Address(0, 0)
Thank you in advance for your help.
Bookmarks