Posts by noeyedeer

    I am using an Input Box to get Interest Rates from my user. I am having problems getting VB to recognise when a date is a Saturday or a Sunday !


    Any ideas how I can amend my code so that if a date is a weekend then simply copy the previous rate rahter than ask for one to be input ??


    While Show_Box = True
    Sheets("INT RATES").Select
    oldval = Range("a9999").End(xlUp)
    Range("a9999").End(xlUp).Offset(1, 0).Value = oldval + 1
    If oldval + 1 = vbSaturday Or vbSunday Then
    ActiveCell.Offset(-1, 1).copy
    ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlValues
    End If
    valdat = Worksheets(6).Range("a5").End(xlDown)
    ' Show the input box.
    Response = InputBox("Enter the interest rate for " & valdat, _
    "Number Entry", , , 1000)



    :puzzled:

    You'll be glad to hear my macro is now running pretty much perfectly and I'm just trying to show off a bit now !


    I would like to get interest rate info from the user and enter this directly nito the correct sheet.


    So when they click a command button an input box will appear, they type in the date and the rate which then enters itself into the correct postion on my RATE sheet.


    I'm sure this can be done but I've never looked at Input Boxes before so I would appreciate a bit of help to get my started.


    :eureka:

    It's because I import this data from some banking software, strip it down to the bare essentials and then export it another sheet. So the pivot table doesn't really come into play.:thumbdow:

    I am trying to get my head round the count function in VB and not having much luck.


    I have a column of data which contains account numbers. There are about 50 different accounts in there and each account number appears about 25 times (once for each day of the month.)


    What I am trying to do is count the occurences of the first account number ni my column and then check that each of the other account numbers appear the same amount of times.


    I thought it would be fairly simple but the count function always confuses me :regan:

    Perfect Dave, Cheers for that.


    Does anyone know of a good reference point that gives a good list of commands like Current Area and the like ?


    There seem to be loads of useful ones that I could do with knowing.


    :flower:

    Ok, a anice easy one.


    I have a rectangle table of data. What's the simplest VBA command to select the bottom row of data in my table and copy it to the empty row below ??


    It's taken me about 5 lines of code but I'm sure there is a much simpler way to do it !! :flower:

    The easiest way is to select the cell that you want the function to be placed in.


    Then select Insert from the top menu and then select Function and choose the function that you want from the list. You can then edit using the function wizard that appears.

    I can't believe it !!


    I have just discovered that everytime I import new data into my workbook all my links are lost and my formulas set to REF!!. Bugger !!!!


    I have a summary sheet which draws it's data from the sums of a list of balances on another sheet.


    These balances are drawn from a different worksheet called "IMP', as are the interest rates from a worksheet called "INT RATES"


    Each month I import a whole new load of data into "IMP' and update the data in 'INT RATES'.


    I realise that when changing data links can be lost but I have set calculation to be manual as I thought this would solve the problem.


    Help !!!! Please........ :(

    I'm working through a range of rows that includes about 100 columns. I'm used to working with only the one column so I'm getting a bit stuck !!


    Whereas I would normally use Activecell to manipulate the data I'm not so sure what I should be using for rows.


    This below sub should just check if the range is empty, if it is then copy data from the above row. Easy I thought, but oh no !!


    any ideas ??


    Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
    ActiveCell.Name = "last"
    Range(Range("last"), Range("b1")).Name = "MyRange"
    Set rng = Range("MyRange")
    Range("B1").Select

    For i = rng.Rows.Count To 1 Step -1
    ActiveCell.EntireRow.Name = "here"
    If ActiveCell = "" Then
    ActiveCell.Offset(-1, 0).Select
    ActiveCell.EntireRow.copy Range("here").Offset(1, 0)
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Next




    :flower: