Posts by hgus393

    Hello,


    I am trying to remove rows if a certain condition is met. More specifically if the row has no value ie only zero the the row should be removed. I am using the countif function to check if the row only has zeros (and if it does then removing it), in the worksheet the formula looks like this:


    COUNTIF(D25:X25;"<>0") ie any cell that renders the amount zero should be removed.


    I am toying with the following code but I does not work..



    Anyone has a clue what I am doing wrong?

    Re: Reopened Vlookup using VBA



    Aha, that works even better!!! Thank you even more :yourock:
    Robert

    Re: Reopened Vlookup using VBA


    Quote from royUK

    Just open the original post & copy & paste from your Explorer address box


    Thank you! I will do this the next time, sorry for the inconvenience!
    :)
    Robert

    Re: Reopened Vlookup using VBA


    Ok,


    To some extent this had to do with the country setting I have, however I have corrected for this but it still does not work...anybody any clues?


    //Robert

    Re: Reopened Vlookup using VBA


    Quote from h1h

    you have to pass the date to the indirect function as text, i.3. indirect(text($a$1;"yyyy-mm-dd") & etc etc


    Sorry but this does not work......

    I had a post with the following title "Vlookup using VBA". I got help, and it worked out fine in the example I uploaded BUT... The sheet names in my project are named as dates and as such referencing these by the indirect formula I get #ref - does anyone really smart know how to get round this?
    I am posting an example file to show you all what I mean.


    //Robert

    Re: Vlookup using VBA


    Alright I am posting an example that will hopefully help you understand what I am trying to do. The sheet names are dynamic and as such I cannot use a simple formula for this, the users are not experienced users of excel and will therefore not be able to correct this themselves. The example consists of 11 sheets and it is the master sheet where I would like to have things done. Ie a macro (or formula if it is possible) that would create a vlookup irrespective of the dynamicity of the sheet names. Hope you understand what I mean!


    :)

    Hello all...again!


    Does anyone know how to do the following?


    I am starting out with a master sheet that contains account numbers, I got help from Wigi ( :thanx: ) to name the columns the same as the worksheets in the workbook.


    What I am trying to do is create a vba that will do a vlookup from the master sheet starting with sheet1 ending with the penultimate sheet and for every new sheet the vlookup formula in the master sheet should move one column to the right, to correspond to the new worksheet.


    Doing a simple record macro of the vlookup formula renders the following:


    Code
    Sub Analysis()
        ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],'Sheet1'!C[-1]:C[6],8,FALSE)"
    End Sub


    The activecell in the formula above is the first empty cell in column C (column C corresponds to the first sheet, column D corresponds to second sheet etc.)


    The actual number of cells to be looked up in the master sheet is 4595 rows.


    //Robert

    Hello,


    I am trying to create vba that puts the sheet name for all the sheets in the workbook (except for the last two) into the last worksheet ranging from the first empty column in row 1 onwards (ie dependent on how many sheets there are in the workbook). Does anyone have an idea how to do this? I could not find anything in the previous posts how to do this.


    In the last workbook I am choosing the first empty column by the following:
    and this is how far I have gotten....

    Code
    Sub ColumnNames()
    Dim NextColumn As Integer
    NextColumn = Range("IV1").End(xlToLeft).Column + 1


    //Robert

    Hello all,


    I'm stuck on a problem! I have a range from (j8 to j28) where some cells have information and some don't. What I am trying to do is copy the cells (in range j8 to j28) that have a value, ignoring the ones that don't have a value, to the next empty cell in the B column - and it does not work for me, what am I doing wrong? The code I am using is the following:

    Code
    Sub Notes()
    Dim NextRow As Double
    Dim Notes As Range
    NextRow = Range("B65536").End(xlUp).Row + 1
    For Each Notes In Worksheets("Sheet1").Range("j8:j28").Cells
        Cells(NextRow, 2).Value = Notes.Value
        Next Notes
    End Sub


    Grateful for any help!! :)

    Re: Eliminating null values


    :thanx:
    That works a lot better, Smart using the used cell as the starting point, never thought of doing this...The more I learn the more I understand that I don't know much :?
    :thanx:
    Robert

    Re: Eliminating null values


    Still does not work!

    Code
    Sub Data
    Range("a1",Range("65536")).End(xlUp)
    Sub


    I added a ) as it was complaining about this but running this I get "Invalid Use of Property"
    ?Robert