Posts by julian_t

    I have played around with quotes, brackets, spaces etc and cannot get this to work.


    However the attached code does work - looks a bit clumsy, but it works! So it uses 90% of your code, so thank you very much.


    H is just the next blank column in the sheet. The formula needs to be in H2, and copied down in that column to the bottom of the data .


    The formula is =if(d2="8 November",G2,F2) - where 8 November is the current month in the posting period, and where the posting period is in column D.

    Yes. I want the code to be stored in the Pivot Master sheet, and to be able to open and run the code in the data sheet.


    But each time one gets a new extract out of SAP, I want to be able to run this code on the new extract, hence keeping the code in a 'master' file.


    Thank you for your forebearance and patience, Roy. its much appreciated.

    There are several columns of data with headings of


    Posting Period , Budget, Forecast, Actual, I want to add a further column to put the forecast in for every month, apart from the current month where I want the actual figure. I can then use that column in a pivot table - so if the current month was October, it would look like this:

    Posting PeriodBudgetForecastActualAct/Fcast
    7 September1000200020002000
    8 October1000220015001500
    9 November1000250002500

    Thank you so much for taking a look, Roy,


    Mth is meant to be the month number, between 1 and 12


    I defined it in the main sub earlier in the same module. So the above problem was part of the sort_1() sub


    I haven't used VBA for about 17 years, so I am beyond rusty!

    I am trying to create a formula which puts into one column the forecast data for every month, apart from the current month which will have the actual data.

    The data is an export from SAP, and it is very annoying in that the order of the columns is not always the same


    So the formula would normally be like =if(d2="8 November",F2,G2) but I cannot be sure that the period will be in column D.


    So I need some code that will identify the column which in row 1 contains the heading "Posting Period".


    Attached is my attempt, which needless to say doesn't work!!


    I have a bit of code that works fine, for finding any error cells. What I am trying to do is to have a similar bit of code, but instead of finding the '#N/A' cells, I want it to find any cells in the range which are not zero.


    Code
    Sub FindNA()
    With Worksheets("Phasing").Range("V6:AC15000")
        Set c = .Find("#N/A", LookIn:=xlValues)
        If Not c Is Nothing Then
           MsgBox "Error in cell" & c.Address, vbCritical, "Project Lookup Error"
        Else: MsgBox "There are no error cells - you may continue", vbOKOnly, "Project Lookups"
        End If
    End With
    End Sub

    I have 2 sheets in a workbook, the first sheet has blocks of data, with totals every 4 rows.


    In the next sheet I want to pick up those totals, but if I pick up the first total with =sheet1!H4, then copy the cell down it will add 1 to the cell ref, so the next cell will pick up H5, then H6 etc. I want it to pick up H4, then H8, then H12 etc.


    It gets rather tedious manually editing the formula, once you get to line 500! There must be a better way.

    I am trying to add numbers of days and hours together, to get a total elapsed time in number of days/hours. This is for the sailing times between two ports.


    I am not concerned with the date at all.


    I have formatted the cells to "dd:hh", but excel doesnt seem to like that - when I put in 10:07 for 10 days and 7 hours, it thinks i mean 7 minutes past 10 on the 1st of Jan 1900.


    I have attached a small sample of data.


    Grateful for any help.

    I have a user form which users must enter their password, and check a checkbox, before clicking a button.


    It works fine, but I would like the form to display the username in a textbox. I can get this to do this, but only after they click the checkbox.


    Is it possible to populate textbox3 as soon as the form opens?


    Code
    Private Sub CheckBox1_Click()
     'Put user initials into TextBox 3
     TextBox3.Value = UserName()
    CommandButton1.Enabled = CheckBox1.Value
    If Len(TextBox2.Value) <> 0 Then
    CommandButton1.Enabled = CheckBox1.Value
    Else: MsgBox "You must input your password"
    End If
    End Sub