Posts by noeyedeer

  • Formula Rookie

    =SUM(B51-A51)


    I think this is what you are looking for.


    Where column A is you plan and column B is yer actual, I inserted the above formula in column C


    :coolwink:

  • A sum in VB ?? CD to the rescue. SOLVED

    This one has got me, thougt it would be a piece of cake !


    I am in a blank cell that has values in the entire column above above it.


    I want to insert the average value of the cells above, but I want to start my average from the cell 10 above and finish it in the cell 3 above.


    Anyone got this one sussed ?? :no:

  • Losing it in the Loop !! SOLVED

    Only me.


    I have written myself a nice little sub to get interest rates from the user and insert these onto my sheet.


    However I am having a problem !!


    Before the input box appears I am checking for various things. One of these is that if the day is a weekend then copy the rate from the day before.


    I have got this in my sub, but my question is this, if the date is a weekend and I copy the previous rate, how can I skip the inputbox bit of my sub and go back to the start of my loop ??


    I have put my code below so I hope the above makes sense, I feel like I'm rambling a bit !


    Show_Box = True


    ' Begin While loop.
    While Show_Box = True
    Sheets("INT RATES").Select
    Range("a9999").End(xlUp).Select
    oldval = ActiveCell
    ActiveCell.Name = "fin"
    If Range("fin").Offset(0, 1) <> "" Then
    Range("fin").Offset(1, 0).Formula = oldval + 1
    newval = Range("a999").End(xlUp).Formula
    If WeekDay(newval) = 1 Or WeekDay(newval) = 7 Then
    Range("b999").End(xlUp).copy
    ActiveCell.Offset(1, 1).PasteSpecial Paste:=xlValues
    End If
    End If
    valdat = Range("a5").End(xlDown)

    ' Show the input box.
    Response = InputBox("Enter the interest rate for " & valdat, _
    "Number Entry", , , 1000)

    ' See if Cancel was pressed.
    If Response = "" Then
    Range("a5").End(xlDown).Clear
    ' If Cancel was pressed,
    ' break out of the loop.
    Show_Box = False
    Else
    ' Test Entry to find out if it is numeric.
    If IsNumeric(Response) = True Then
    ' Write the number(/100) to the selected cell
    Range("b5").End(xlDown).Offset(1, 0).Value = Response / 100
    'Show_Box = False
    Else
    ' If the entry was wrong, show an error message.
    MsgBox "Please Enter Numbers Only"
    Range("a5").End(xlDown).Clear
    End If
    End If
    ' End the While loop.
    Wend

  • How do you disable the "X" ? - SOLVED!

    Check out the link :-


    http://www.erlandsendata.no/en…/dialogs/preventclose.php



    or use this code......


    If you want to prevent a Userform from closing when the user clicks the x-button in the top right corner of the dialog, you can add the macro below to the UserForms code module:


    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "You can't close the dialog like this!"
    End If
    End Sub


    This will also prevent the dialog to be closed when the user presses Alt+F4 on the keyboard.

  • using vb to collect data from multiple worksheets

    =MATCH will match a given number or text, to a range and bring back it's position in that range


    so, for example, =MATCH("John",A1:A4,0)


    will bring back a result of 3 from the following range :


    Andy
    Mike
    John
    Paul


    as John is 3rd in that range


    (the 0 at the end of the formula specifies an exact match - there's other options, but not just now in this example)


    so we can use the MATCH function to bring back a number........ in your case, we matched the account number on yoru summary to the acoount number in your detail - it bringing back the position in your range of account numbers.


    Each account will be different (increasing by 2 columns each time), however, just remember that at this stage that each account brings back a new number


    the OFFSET command says the following :


    starting at a cell I specify, move down x number of rows, move accross by y number of columns and bring back a range of cells t amount deep and v amount wide :


    OFFSET(A1,3,4,5,6) says starting at cell A1, move down by 3 rows, accross by 4 columns and bring back a range that is 5 deep and 6 wide.


    In yoru example though, we are saying :


    start at IMP!A1, move down by 0 rows and accross by "the above match value" and bring back a range that is 1 cell deep and one cell wide.....


    since "the above match value" is how ever many columns acrross our accoutn number is, it will bring back the balance for that acount number for that date


    due to the anchoring : as we copy the formula over, the account numbers change, offsetting by more columns and as we copy it down, our reference to the date changes so it adjust the rows....


    this very probably reads as gobbledygook, but try it a few times - when it makes sense, it's a great technique to use


    :)

  • using vb to collect data from multiple worksheets

    Have you looked at the OFFSET and MATCH functions ?


    I have just completed a similar task and Chris Davison (one of the main men on this board !) advised this was the best way to go, and it certainly was for me.


    Have a look at those functions and drop Chris a U2U message using this board and he can explain them to you really well.


    They basically enable you to link to a cell on another sheet that is always moving.

  • Struggling with Days !!! SOLVED

    It seems I was a bit hasty....


    Hmmm, I was a bit hasty there.


    I have changed my code but it still won't recognise the sunday or saturday.


    If I type ?weekday(range("fin")) in the available window it gives me the correct day number, whether it be 5, 2, 7


    Any more ideas, 'm sure I read in the MS help site about problems with dy numbers but I can't seem to find it now.
    :no: