Posts by jproffer

    Re: VBA CODE for Excel 2007 & 2010

    Do you want this input box to be a seperate action from the copying you're doing now?

    As in, have to to copy as before and one to get a user input?

    Sounds like, so I'll work on that. If not, please advise.

    Re: Clear Cell in column F if cell in D is cleared

    This seems to do what you want, under mild testing. Test it out and back if you have trouble.

    Re: Sheet Name in a cell

    Summary Sheet, cell A1:
    =MID(CELL("filename",'Sheet A'!A1),FIND("]",CELL("filename",'Sheet A'!A1))+1,31)

    Summary Sheet, cell A2:
    =MID(CELL("filename",'Sheet B'!A1),FIND("]",CELL("filename",'Sheet B'!A1))+1,31)

    as long as the file has a path (I.E. - has been saved to file), this will work.

    Re: Resizing working area (stop scrolling through empty rows)

    I was taking a look at this and noticed that code above is only good for columns up to "Z" (single letter columns). Hopefully you haven't found this out the hard way yet, but here is a better one that takes multi-letter columns into account:

    Re: Resizing working area (stop scrolling through empty rows)

    Simplest way is to run this:

    Please test on a copy first to make sure nothing you need gets deleted. :)

    Re: Add Spaces in a string of numbers

    This can be done with a formula. Try:

    =IF(LEN(A1)=6,LEFT(A1,2) & " " & MID(A1,3,2) & " " & TEXT(RIGHT(A1,2),"00.00"),LEFT(A1,3) & " " & MID(A1,4,2) & " " & TEXT(RIGHT(A1,2),"00.00"))

    assuming the first one is in A1.

    Re: Workbook Expiration

    This is how I always do it. Of course you have to lock the VBA project, and you have to change the ExpDate for each release of the workbook.

    This is from memory, but it looks right :)

    Re: Defined Function does not work

    Ok then. I don't know what else to do without more details. Sorry.

    If that's not what you want...instead of "don't work", maybe you could explain what you DO want to happen.

    Re: Defined Function does not work

    Sub MyValue()
    Dim MyVar As Variant
        MyVar = Range("B9").Value
            MsgBox MyVar
    End Sub

    I don't think this is what you want, but it's a shot in the dark because "I want the result of B9 in VBA" could mean anything.

    Re: Defined Function does not work

    Figured that :)

    The function isn't in your workbook. So you couldn't use it there, even if it was well formed, which it's not.

    Other than "trying to use the function", what are you trying to do...with your data??

    You wanted to find the max based on date, and you did that in cell B9. If you're trying to insert that formula in a cell, you will have to use a sub not a function.

    Re: Conditional formatting, deleting first character in a cell but keeping the format

    In conditional formatting, choose "Use a formula to....." and enter:


    Set your format.

    add the other 2 formats.

    $B1 refers to whatever cell the value is actually in. It may seem like a circular reference, but in this case it's not.

    You should be able to drag it down and then select Fill Formatting Only