Posts by Egad

    This is a method of referencing cells that I haven't seen mentioned in the forums (some one will correct me if I'm wrong) :)
    It's saved me a few times and is a great way of looping thru a range of cells in code or picking an offset cell value.

    range("b2:d3") (1,1).value = (value of b2)
    range("b2:d3") (2,2).value =(value of c3)
    range("b2:d3") (6,1).value= (value of b7)
    range("a1:a10") (2).value = (value of a2)

    Credit to:

    Punch cards. :) I used to be an IBM 1401/7074 operator feeding it massive decks of cards. The insurance co. I worked for had a whole room of key-punch operators. The IT schools at the time were all running ads about the wonderful career opportunities of key-puch operators. A different era.

    If you use this approach be mindful of the implications on other users:
    "The ExclusiveAccess method saves any changes you've made to the workbook and requires other users who have the workbook open to save their changes to a different file."
    I assume the macro wouldn't need exclusive access for too long. I am unclear if other users would have to "save changes to another workbook" only during the macro life-span or afterwards also.

    I have the same problem importing from CSV sheets and use a conversion macro similar to this.
    This macro converts the cells in place but can be modified to place into an adjacent column.

    Sub DateCon()
    For x = 1 To 100
    If IsDate(Cells(x, 1)) Then
    Cells(x, 1).Value = "'" & Month(Cells(x, 1).Value) & "-" & Day(Cells(x, 1).Value)
    Cells(x, 1).Value = Cells(x, 1).Value
    End If
    Cells(x, 1).NumberFormat = "@"
    Next x
    End Sub

    Yes, asap-utilities is great, I'd recommend it also, but it is an add-in and I am certain that none of my clients have this installed. It makes my life less complicated to put relevant UDFs into a spreadsheet module instead of asking clients to download A-U.

    [quote=IF(ISNA(VLOOKUP(E2,A$2:B$4,2,FALSE)),"Not Found",VLOOKUP(E2,A$2:B$4,2,FALSE))

    The long error trapping code required in Excel has always bugged me, especially having to write the vlookup twice as in the example above.

    That's why I created & use this UDF function:

    Place this code into a VBE module:

    Function ErrFix(formula, result)
    If IsError(formula) Then
    ErrFix = result
    ErrFix = formula
    End If
    End Function

    ..and on the spreadsheet use this formula:
    =ErrFix(formula, error result)

    =ErrFix(VLOOKUP(F5,C2:D12,2,FALSE),"Not found")

    It can also be used to trap #div/0 errors(returns a 0 or blank if e3 is 0.)

    OK, I think I have it now, I hope so, let me know... :)




    in the top example if a 2 is in a1 then it will average(average!e1:e50)
    in the top example if a 2 is in a2 then it will average(average!e2:e51)

    Hi skersch,
    You cannot automatically disable the 'enable macros' pop-up. This is a security feature to protect users from malicious macros'. It would be like an internet download that automatically disabled your firewall or anti-virus.

    It is up to individual users to select their security settings.
    Under Tools-Macro-Security users may set their macro security to High, Medium or Low. High disables all macros, Medium gives the pop-up alert, and Low lets all macros run without the pop-up alert.

    John, welcome to the Forums.
    Something like this?

    Load UserForm1
    UserForm1.TextBox1.Value = Range("a1").Value
    UserForm1.TextBox2.Value = Range("a2").Value

    ...and to save back to cells...

    I hadn't noticed that before. It seems to happen only when the zoom is reduced (ie 75%).
    You can change font size by right clicking on the button when you are in design mode. This will pull up the Properties window that has a font attribute line. You could also try resizing button (bigger).

    I'm sure this isn't the prettiest way to do it but it works. Is this what you are looking for?

    Function Sumprodx(teamname As Range, TeamVal, outcome As Range, OutVal)
    Sumprodx = 0
    For x = 0 To teamname.Rows.Count - 1
    a = Cells(teamname.Row + x, teamname.Column).Value
    b = Cells(outcome.Row + x, outcome.Column).Value
    Sumprodx = Sumprodx + (a = TeamVal) * (b = OutVal)
    Next x
    End Function

    Sub Test3()
    z = Sumprodx(Range("whatever1"), 1, Range("whatever2"), "w")
    MsgBox z
    End Sub

    Using sumproduct as a worksheet function in VBA only seems to work in its simplest form (the example below works) but not when it gets more complicated ie teamname=1 * outcome=w

    answer = Application.WorksheetFunction.SumProduct(Range("teamname"), Range("outcome"))

    The following quick & dirty code works also:

    Sub test2()

    TeamVal = 1
    OutVal = "W"
    y = 0
    For x = 0 To Range("TeamName").Rows.Count - 1
    a = Cells(Range("teamname").Row + x, Range("teamname").Column).Value
    b = Cells(Range("outcome").Row + x, Range("outcome").Column).Value
    y = y + (a = TeamVal) * (b = OutVal)
    Next x

    MsgBox y
    End Sub

    Hi scharles, welcome to the Forums.
    The code below deletes all page breaks on the page then recreates them at each line in column A where it finds text "xxx". You can change "xxx", and the "xxx" can even be imbedden in a longer string ie " New Pagexxx number".

    Hope this is what you are looking for. The other approach that I can think of offhand is more complicated and would be for the macro to loop & find the first line with "xxx", define the print area, print, find the next line with "xxx", redefine the area, print etc...loop, loop,loop...

    Sub PageBreaker()
    Cells.PageBreak = xlPageBreakNone
    With Worksheets("sheet1").Range("a1:a500")
    Set c = .Find("xxx", LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Worksheets("Sheet1").Rows(c.Row).PageBreak = xlPageBreakManual
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With
    End Sub