Posts by J88L

    Re: Populate Cell Using VLookup Worksheet Function


    Yes, I would expect that this is what it means, but cell that has the following formula works:


    =VLOOKUP(B3,DateRange,2,0)


    That's my delimna... it works if I use a formula, but it does not work in VBA.

    Re: Seperate Date From Time In A Cell


    you could round down to the nearest integer.


    Added by admin


    Searched the forums for similar issues and I cannot seem to work this out.


    If I put this in a cell it works fine


    =VLOOKUP(B3,DateRange,2,0)
    If I tell VBA to write that formula, it works fine.

    Code
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-44],DateRange,2,0)"


    If I put this in VBA:

    Code
    Range("AK1").Value = Application.VLookup(Range("B3").Value, Range("DateRange"), 2, False)


    I get #N/A. I'm stumped - any ideas why this would happen, or how I can work this into my code? I need the vlookup to occur 1000s of times, and don't want to have to put the formula in the spreadsheet because I just want the results, not the formula.

    I'd like to run some simulations - sure this has been done before.


    Let's say demand historically for December is mean 10,000 units with a standard deviation of 1,500. I would like to create a random number based on that data. Any suggestions?

    Re: Select Worksheet


    The workbook has different worksheets for different forms.


    When the user opens the file, a menu worksheet is selected while all others are hidden. Using buttons, the appropriate page needs to be shown & selected.

    This should be easy, but I'm brain-gapping.


    I want to select a specific worksheet by it's VB name. In other words, if the sheet comes up as: [COLOR="Red"]Sheet3[/COLOR] [COLOR="Blue"](ESF)[/COLOR] in the VB Editor, I would like to choose it using the [COLOR="Red"]Sheet3[/COLOR] designation. I can do it using the [COLOR="Blue"](ESF)[/COLOR] designation using:

    Code
    Sheets("ESF").Select


    But I prefer to use [COLOR="Red"]Sheet3[/COLOR] so when the users change the name, the macro still works.

    Re: If Sheet Protected Then...


    I'm not leaving the code easily accessible, but I'm also not going to great lengths to secure it, either. If an end user somehow finds their way into the macro menu and attempts to run a macro while the worksheet is protected, I'd like that user to get a polite message telling them to buzz off. :p

    Hi, Excel gurus


    What code would I use to test if the sheet is protected or not?


    In other words:


    Code
    If Activesheet.Protection = True Then
       MsgBox "You have no authority!!!"
    Else
       ' Some code here
    End If

    Re: Hide Drawing Objects


    Thanks, this is great.


    If I want to differentiate on the fill depending on the shape type, is that something I can do? I'm trying to prevent a freeform line from receiving a fill.


    Code
    Sub unhideShapes()
        For Each sshape In ActiveSheet.Shapes
            If MsoShapeType.msoFreeform Then
                ' nothing
            Else
                sshape.Fill.Visible = msoTrue
            End If
            sshape.Line.Visible = msoTrue
        Next sshape
    End Sub


    (THAT doesn't work!) :p

    I'm looking to write two small macros that will either hide or show all lines, freeform lines, and autoshapes on a page. In my extreme competence, I determined a for/next loop would be appropriate... but other than that, I can't figure out where to start. Any suggestions?

    Re: Hide Rows From Pivot Table


    If I were going to attempt this, I'd take a short cut and add an additional column. Maybe label this column, "Calculate?" Then you can filter the "Y" or "N" entries you made on your pivot table. But maybe there's a more clever way to do this? :)

    Is there a maximum value as to hwo many characters will wrap in a cell? I have an employee that is running into a problem with her text that quits wrapping after she reaches a certain point. Wondering if it is a design limitation, or if I should look elsewhere to try to solve it.

    I am looking to build some permanent desk areausing component cabinet furniture, and want to put the CPU in a closed cabinet. Not wanting to melt anything, I thought a fan on one end of the cabinet and a small vent to draw air in on the other would be a nice way to keep things cool in there. I'm not finding an off-the-shelf product to help. Anyone ever do this or no of a product?

    Re: Sort Pictures With Cells


    Hi, Dave, I assume you're going for the Move and size with cells selection? Please correct me if I'm wrong; that does not seem to have any affect on my issue. The pictures move if I cut and paste cells, but not if I sort the table.