Posts by Kieran

    Re: Excel VBA - Array help (convert code to an array)


    bremen,


    this is untested, but it may give you a start


    Re: Valid Function - better idea?


    CallMeJD,


    try this

    Re: outputting calculations and moveafterreturn not working


    The moveafterreturn property relates to the direction that the active cells is selected after the return key is pressed.
    In a macro keys are not pressed (you could use sendkeys - but dont) so the setting has no effect.
    If you want to move to the next cell use something like


    ActiveCell.Offset(1, 0).Activate

    Re: Updating averages VBA


    Try the last line with extra spaces like


    Cells(4, 7).Value = "=AVERAGE(G5:G" & next_cell & ")"
    It seems the intepreter was the problem, not your code

    Re: SetFocus on TextBox as first control in a frame


    Batman,


    Have you tried creating another control that receives that focus in the activate/initiiaise event, then transfer the focus to the textbox and then hide the origianally focussed control.
    This will avoid the prboblem where your form has only the one textbox control visible and also work where the tere are multiple controld visible.

    Re: Formula error


    stan,


    The formula =SUMPRODUCT((A2:A65534=G1)*(C2:C65534=F2)*(D2:D65534)) will work.
    Sumproduct does not like using the whole of a column (eg C:C), so you have to specify a lesser range

    Re: Saving files using loops in VBA


    Try (this is untested..)


    Private Sub CommandButton1_Click()
    ConvertFiles
    End Sub


    Private Sub OpenTxtFile(FName As String)
    FName = FName & ".txt"
    Application.DisplayAlerts = False
    ChDir "J:\txtdocs\actual"
    Workbooks.OpenText Filename:= _
    "J:\txtdocs\actual\rptResourcePlanHistoryye.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1))
    Application.DisplayAlerts = True
    End Sub



    Private Sub SaveXlsFile(FName As String)
    FName = FName & ".xls"
    Application.DisplayAlerts = False
    ChDir "J:\execldocs\actual"
    ActiveWorkbook.SaveAs Filename:= _
    "J:\execldocs\actual\rptResourcePlanHistorycorrye.xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    End Sub


    Private Sub ConvertFiles()


    Dim Fnames(1) As String
    Dim i As Integer


    Fnames(0) = "J:\txtdocs\actual\rptResourcePlanHistoryye"
    Fnames(1) = "rptResourcePlanHistoryye"


    For i = 0 To UBound(Fnames)
    OpenTxtFile (Fnames(i))
    SaveXlsFile (Fnames(i))
    Next
    End Sub

    Re: macro is giving an error on lock worksheet


    When using a macro to insert values to a locked and protected sheet, you need to unprotect the sheet, then enter the data, then reapply the protection.
    This can be done within the macro, and occurs so quickly that the users will not have an opportunity to amend the data.

    Re: Group search


    Mac See,


    My apologies, I set the lokup indicies up incorrectly.


    Look at line 128 - the row number should be 10 not 9.


    The formula shoud now then work.

    Re: printing multiple copies in macro


    Insert the line


    msgbox Activeworkbook.Sheets("Store Summary").cells(65, 3).value & " " & isnumeric(Activeworkbook.Sheets("Store Summary").cells(65, 3).value)


    in the subroutine.
    It will indicate the value, and whether it is a number or not.


    If you have the correct value, and it is not a number then it needs to be converted using val(Activeworkbook.Sheets("Store Summary").cells(65, 3).value)or similar.

    Re: printing multiple copies in macro


    Cells(65,3) equates to row 65, column 3 or or C65 in normal terms.


    Is that the correct cell that you are using for entry, if not the value will be 0, and trigger the message that you experience.


    you can amend the line to read
    Activeworkbook.Sheets("Store Summary").PrintOut Copies:=Activeworkbook.Sheets("Store Summary").[c65], Collate:=True


    if it helps identify the correct cell

    Re: printing multiple copies in macro


    Try


    Activeworkbook.Sheets("Store Summary").PrintOut Copies:=Activeworkbook.Sheets("Store Summary").cells(65, 3).value, Collate:=True
    Sheets("Sales Cube").Select
    End Sub


    Untested - but hopeful...

    Re: copy conditional formatting


    Go to the toolbars, customise menu, select the commants tab, format options, The format painter is the 10th item down the scrolling list. Drag the icon to your toolbar and it is done.


    it may work without this, if you use the paste special>format option.

    Re: copy conditional formatting


    It seems that you have set the references to absolute, so try changing


    condition 1 =and($A$1="a";$b$1="a") put the selected cells a selected (A1 to K1) in red to tell me i make the error of selecting A1 and B1
    condition 2 =$A$1="a" put my selection in yellow
    condition 3 =$b$1="a" put my selection blue



    to


    condition 1 =and($A1="a";$b1="a") put the selected cells a selected (A1 to K1) in red to tell me i make the error of selecting A1 and B1
    condition 2 =$A1="a" put my selection in yellow
    condition 3 =$b1="a" put my selection blue


    and then copy the format down using the format painter tool.

    Re: Count cells in non-zero batches


    Greg,


    In the count column, put =IF(AND(A1<>0, A2=0),COUNTIF($A$1:A1,">0"),0) in the first cell and =IF(AND(A2<>0, A3=0),COUNTIF($A$1:A2,">0")-SUM($B$1:B1),0) in the second cell and copy down.


    These formulas assume that you data ins in cell a1 down, and the calculation is in column b adjacent, adjust the references as appropriate.