Posts by ICdeadppl

    Hi

    I'm having trouble getting my formula to work. I'm trying to bring into excel the formula so if ppl remove blank columns the correct average is calculated.

    colcnt counts the no of columns in the data range but includes 2 description columns thus I'm trying to remove them with the -2.

    Code
    ActiveCell.FormulaR1C1 = "=RC[-1]/COUNTIF(R1C1:R1C" & colcnt," <> """)-2""



    any thoughts would be appreciated.

    Re: Text to Columns (Column Deletion)


    There is no repeat pattern. I could write the code to transfer the column to another sheet then I wouldn't care how many columns it speads the data to, but I was looking for an efficient way code wise of effecting this.

    Hi all

    I am using a space as my delimiter for seperating the cost account from the description as per below within a macro. The description is removed via the "Do not import column (skip)" option but due to using the space as the delimiter I need a way to determine the no. of columns to skip. Because I'm doing this in the column the data is in, I don't want it to post data outside of this column, otherwise it will corrupt the data of the subsequent columns.

    166.6007.4.CMSHW.101 Blah 1 blah blah (4 columns to skip)
    166.6007.4.ILT.201 blah blah / blah blah - blah 2 (8 columns to skip)


    Im currently using this as a solution but would prefer some sort of check if possible


    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
    :=" ", FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, _
    9), Array(6, 9), Array(7, 9), Array(8, 9), Array(9, 9), Array(10, 9), Array(11, 9), Array(12, 9)), TrailingMinusNumbers:=True


    Any thoughts?