+ Reply to Thread
Results 1 to 7 of 7

Efficency and Varible Column in a formula

Hybrid View

  1. #1
    Registered User
    Join Date
    10-13-2020
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Question Efficency and Varible Column in a formula

    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 :

    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
    As you can see, there is a lot of If statements inside one another.
    SelectedMonth is a label defined by Left(ActiveCell.Value, 3) so that will change per run of this subroutine.
    When I tried to use
    IF SelectedMonth = "May" OR "Jul" OR "Oct" OR "Dec"
    it 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.


    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.

        '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)
    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.

    Thank you in advance for your help.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Efficency and Varible Column in a formula

    These syntaxes work

    If SelectedMonth = "May"  Or SelectedMonth = "Jul" Or SelectedMonth = "Oct" Or SelectedMonth = "Dec" Then
            If SelectedMonth = "May" Then
                 Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1
            ElseIf SelectedMonth = "Jul" Then
                 Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1
            ElseIf SelectedMonth = "Oct" Then
                 Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1
            ElseIf SelectedMonth = "Dec" Then
                 Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1
            Else
                 Countdowntoday = (SelectedDay + 31) - ActiveCell.value + 1
            End If
    Select Case SelectedMonth 
         Case "May", "Jul", "Oct", "Dec"
              Countdowntoday = (SelectedDay + 30) - ActiveCell.value + 1
         Case Else
              Countdowntoday = (SelectedDay + 31) - ActiveCell.value + 1
    End Select
    I didn't understand the 2nd question.
    Last edited by AlphaFrog; 10-21-2020 at 01:09 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    10-13-2020
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Re: Efficency and Varible Column in a formula

    That's awesome. Thank you for the solution to problem 1. Basically for the second issue I want the column address not the column number and put it in " " on cell.Value =. I'll try and reword it.

  4. #4
    Registered User
    Join Date
    10-13-2020
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Re: Efficency and Varible Column in a formula

    Ok. So I was hoping to be simple and sweet explaining my second problem but I don't think I can. So please be patient with me.

    I have a set of labels, for arguments sake we'll use DecFirstDate and DecLastDate. I want to make a formula using =sum("columnofDecFirstDate"13:"columnofDecLastDate"13). Basically I know the row for the sum will always be 13. But the column will change depending on the address of Dec Dates. Earlier in the program I do
    DecFirstDate=activecell.Address
    and
    DecLastDate=activecell.address
    to create the labels.
    I did try
    range("w1").value = "=sum(" + "range("decfirstdate").column + "13:" + range("declastdate") + "13)"
    but that's didn't work
    I can pull whatever values for Decdates I need, such as ActiveCell.column earlier in the program if I must as I select them cells to get the address data.
    I would use Left(decfirstdate,1) to input it as string, but there are too many variables, such as A1, V11, DS1 or BB43. And I can't use a
    if Len = < 3 then
    Left(decfirstdate, 1)
    Else
    Left(decfirstdate, 2)
    because sometimes 3 digits has 2 letters or 2 numbers.
    In summary, I want to make a sum formula for the column width of two selected cells but for row 13 as they will always select above row 13 but could be any row above and of varying widths.

    I'm sorry if this still does not explain the problem clearly.
    Last edited by smiler6594; 10-21-2020 at 02:06 AM.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Efficency and Varible Column in a formula

    I understand you want to sum row 13 between the columns of two selected cells which may not be selected in row 13. Correct?

    The part I don't understand is if you reference ActiveCell for both DecFirstDate and DecLastDate, that's the same cell address. There is only one active cell regardless if you select more than one cell.

    Setting that aside for the moment, you can use column Numbers to define a range...

    Dim DecFirstDate As Long, DecLastDate As Long
    DecFirstDate=activecell.Column
    DecLastDate=activecell.Column
    
    Range("w1").Value = Application.Sum(Range(Cells(13, DecFirstDate), Cells(13, DecLastDate))

  6. #6
    Registered User
    Join Date
    10-13-2020
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    Re: Efficency and Varible Column in a formula

    Ah I understand.
    Yes that is correct.
    I apply the label at different points of the program to essentially save the selected cell position to it. So they end up with different values by the time the program is at the point I want to use them.
    But it looks like you've got the solution to what I was stuck on.
    Thank you so much.

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Efficency and Varible Column in a formula

    You're welcome. I figured it was something like that with your ActiveCell.

    For future reference, you can get the column letter(s) from a cell address like this...

    MyColumnLetters = Split(ActiveCell.Address, "$")(1)
    I'll let you discover on your own what's going on there.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup formula except include "other" as a varible
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-07-2018, 11:58 AM
  2. Saving Array formula result in a Varible
    By pankajkaushik12 in forum Excel General
    Replies: 2
    Last Post: 01-25-2017, 09:17 AM
  3. Insert row if efficency improvements
    By cossie2k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2014, 03:59 AM
  4. [SOLVED] Formula for a cell value to show as blank till the varible has been entered
    By arkadd61 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-27-2013, 09:46 PM
  5. MACRO Efficency
    By MZing81 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-29-2012, 01:22 PM
  6. Code to use a varible integer inside a formula in VBA
    By westonkw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-28-2008, 04:19 PM
  7. Offsetting a varible??
    By Ashley Milford via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1