"IF the row has values, do..."

  • Hello,


    I am fighting to implement the following Pseudocode in VBA.


    I have a certain range of an Excel Sheet, e.g. 'A4:J9'.


    "Look into each row in this range. If there is one or several values in a cell of a row, [do something]."


    Any help is appreciated.



    Cheers
    Juergen

  • Re: "IF the row has values, do..."


    Dim a variable as an integer, let it run from 4 to 9. Then


    Code
    Sub countblank()
        
    Dim i As Integer
    For i = 4 to 9
        If WorksheetFunction.CountBlank(Range("A" & i & ":J" & i)) >= 1 Then
            'put code
        Else
        End If
    next i
    End Sub


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: "IF the row has values, do..."


    Thanks.
    I tried

    Code
    Dim y As Long
                    For y = lStart To lEnd
                        If WorksheetFunction.CountBlank(ActiveWorkbook.Worksheets("Sequence Data").Range("B" & y & ":M" & y)) >= 1 Then
                             ActiveWorkbook.Worksheets("Sequence Data").Range("N" & y).Value = ActiveWorkbook.Name
                    
                        Else
                        End If
                    Next y


    I think that the code counts the blanks. If there are more than one blank cells in a line, then the Name of the Workbook is added in another column.


    What I initially searched for is, "is there any cell with a VALUE in a line". If yes, add the name of the workbook.


    Perhaps something like

    Code
    If WorksheetFunction.CountA(.Rows(y)) > 0 Then


    would be good, but this Function is not supported by the method...



    Cheers
    Juergen

  • Re: "IF the row has values, do..."



    Sorry, was a bit too fast.


    Change to:


    Code
    Dim y As Long
                    For y = lStart To lEnd
                        If WorksheetFunction.CountBlank(ActiveWorkbook.Worksheets("Sequence Data").Range("B" & y & ":M" & y)) < 12 Then
                             ActiveWorkbook.Worksheets("Sequence Data").Range("N" & y).Value = ActiveWorkbook.Name
                    
                        Else
                        End If
                    Next y


    as there are 12 columns in between B and M, these included.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: &quot;IF the row has values, do...&quot;


    solved it -

    Code
    If Application.WorksheetFunction.CountA(ActiveWorkbook.Worksheets("Sequence Data").Rows(y)) > 0 Then


    thanks,
    Juergen

  • Re: &quot;IF the row has values, do...&quot;


    What appens in the columns next to column M? These are included too in your last code.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • columns next to M


    Generally, the filename is written into column N, when there is data in the row.
    Then a section is copied into a MasterFile for further data export.


  • Re: columns next to M


    If you don't delete the contents in column N for rows 4 to 9, counting the number of arguments by Rows(y) can be erroneous if indeed there are filenames in these cells. Don't know if that's an issue, but if it is, you should perhaps ClearContents at the end of your code. Or use the Range B to M, not the whole row.

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: &quot;IF the row has values, do...&quot;


    The code shown is part of a function, which openes workbooks in a folder, processes out different data sections (as working like this code example), and then closes the workbook without saving. The filename in column N is therefore no problem.


    If interested, I can post the whole function for better understanding - perhaps you can make a comment to my code, as I consider myself not as an advanced programmer?


    Ah well - here it is (comments still have to be improved):

  • Re: &quot;IF the row has values, do...&quot;


    Me neither an advanced programmer. Just learnt some things on my own in the past few months. I can have a look at it this evening. Or other experts over here can have their say on it.


    Greets
    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!