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:


    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

    If I tell VBA to write that formula, it works fine.

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

    If I put this in VBA:

    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:


    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:

    If Activesheet.Protection = True Then
       MsgBox "You have no authority!!!"
       ' 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.

    Sub unhideShapes()
        For Each sshape In ActiveSheet.Shapes
            If MsoShapeType.msoFreeform Then
                ' nothing
                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.