Posts by Luke M

    Yep, just need to know where you're going. Command line to take you anywhere is something like

    Code
    Application.Goto Workbooks("Some book.xlsx").Worksheets("Some sheet").range("B4")
    Call MyMacro

    Would need to use an event macro. You can right-click on the sheet tab, view code, and paste this in. Modify as needed to match your situation.

    Welcome to the forum!: This may be tough to enforce, as you should consider possibility of the decimals being 0. I.e., to a computer, the numbers 12.3 and 12.30 are the same, but I'm not sure if the first would meet your criteria. Perhaps we should think about it the other way. If user doesn't input enough decimals, you can assume the trailing decimals would be 0. If they enter too many, is that a problem?


    Overall, why are we testing for this?

    Unfortunately, that type of event macro only picks up hyperlinks in cells, not ones assigned to shapes. :(


    If you still want to use shapes/buttons, you'd need to use a specific macro like

    Code
    Sub Test()
        Dim rngDest As Range
    
    
        Set rngDest = Worksheets("Sheet2").Range("C200")
        Application.Goto rngDest, True
    End Sub


    Or, if you switch to just regular hyperlinks in cells (perhaps format the cell the "look" like a button), then your code would be:


    Code
    Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
        Dim myAdd() As String
        myAdd = Split(Target.SubAddress, "!")
    
    
        Application.Goto Worksheets(myAdd(0)).Range(myAdd(1)), True
    End Sub

    You're very welcome. I'm wondering if maybe you didn't see the 2nd and 3rd line of the formula?


    =0.76*SUM(LOOKUP(B2,'Monthly PV'!$B$1:$F$1,INDEX('Monthly PV'!$B$3:$F$50,MATCH(A2,'Monthly PV'!$A$3:$A$50,0),)),
    LOOKUP(C2,'Monthly PV'!$G$1:$J$1,INDEX('Monthly PV'!$G$3:$J$50,MATCH(A2,'Monthly PV'!$A$3:$A$50,0),)),
    LOOKUP(D2,'Monthly PV'!$G$1:$J$1,INDEX('Monthly PV'!$G$3:$J$50,MATCH(A2,'Monthly PV'!$A$3:$A$50,0),)))


    The 2nd line is what's getting the corresponding value for C2; it looks in col G:J of the Monthly PV sheet. Similar for 3rd line, it's looking at D2 and fetching value from G:J as well.

    We can solve half the problem by removing the self-referencing sheet names from the formulas, and just have the cell address. To make your formulas more robust on looking things up from the refrence sheet, you could use the power of lookup formulas to let XL figure out which column and row it needs to be looking at.


    In the attached, I first changes all your dates to be real dates, rather than just strings. Then, I setup a lookup range on your Monthly EV sheet. Last, I changed the main formula in col F to use lookup formulas to figure out which column/row to extract data from. All of the year sheets have the exact same formulas, no need to change anything when you copy the sheet. There's even a formula on the worksheet that pulls in the sheet name, so it knows what year you're trying to find.

    Testing if tags will auto-indent code.


    Using CODE tag

    Code
    Sub Test()
    Dim x as Long
    x = 5
    If x < 5 Then
    x = 10
    End If
    End Sub


    Usinng VB tag
    [vb]
    Sub Test()
    Dim x as Long
    x = 5
    If x < 5 Then
    x = 10
    End If
    End Sub
    [/vb]


    Using vba tag
    [vba]
    Sub Test()
    Dim x as Long
    x = 5
    If x < 5 Then
    x = 10
    End If
    End Sub
    [/vba]


    [VBA]
    Sub Test()
    Dim x as Long
    x = 5
    If x < 5 Then
    x = 10
    End If
    End Sub
    [/VBA]

    Something like this perhaps?

    Can you elaborate on what the formula is intended to do? It's simulatenously checking 3 cells for a specific string, but there's nothing there currently that handles the array output. If the overall goal is to get difference of D3 - C3, then you should be able to just change the cell format of C3:D3 to whatever format you want (e.g. h:mm AM/PM for example) and the formula would still work.

    [VB]
    Sub MyMacro()
    Dim x as Long
    x = 1
    debug.print x
    End Sub[/VB]


    Code
    Sub MyMacro()
    Dim x as Long
    x = 1
    debug.print x
    End Sub


    [VBA]
    Sub MyMacro()
    Dim x as Long
    x = 1
    debug.print x
    End Sub[/VBA]

    Try it like this

    Re: Delete multiple rows with zero or blank


    Hello Busted234, and welcome to the forum.


    My first idea would be non-macro, but to use a helper column and formulas. In helper column put this formula:
    =AND(C2=0, D2=0)


    Filter this column for TRUE, then delete all visible rows. Remove filter, then delete helper column


    If you're still wanting a macro, you could record yourself doing the above and you'll have a pretty good start.

    Re: Can I increase speed performance on this value copy loop?


    So, it looks like you're using some cells as a processing tool, just feeding 50 cells at a time through them. What's the calculation time for this process? Do you have volatile formulas in your workbook that are slowing things down?
    I optimized the code a tiny bit, but not enough to see significat change. The 50 cells at a time bit is definitely your choke-point. Options are to speed up worksheet formulas, or process more than 50 at a time I'd guess at this point.

    Re: index not returning all values instead #NUM


    YOur current formula structure is more if you were returning items for a single cirtieria. Since your's keeps changing, change formula in B4 to this (still an array)


    =INDEX(appno,SMALL(IF(acctname=C4,ROW(acctname)),COUNTIF(C$4:C4,C4)))


    Then copy down.


    Also, might want to change definition of acctname to only be $A$1:$A$3000, rather than all 1 million rows. Would help decrease your calculation load a lot.

    Re: Splitting activity duration by half an hour intervals


    Hi rafuk,


    I'm not sure about the boundary conditions (if start/end time is exactly on the half hour) how things should be handled, but I think this is a good start. I also changed the formatting to display duration, rather than time. If statement checks if the time slot is the first or one of the middle slots. If it is, divide the total duration by the number of periods that fit within our range. Otherwise, be blank.

    Re: Vlookup 1004 error


    Taking a guess, are you sure that the Vlookup will always be successful? If the VLookup can't find the search item it'll return an error and crash the code.

    Re: If two columns don't match, date stamp a 3 column


    Hi Kevin, and welcome to the forum! :)


    I think this will do what you ask.