Posts by Kieran

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


    this is untested, but it may give you a start

    Re: Valid Function - better idea?


    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


    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


    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()
    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
    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))
    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


    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


    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


    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.