Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    19

    Red face Unanswered: looking for empty workbook in a spreadsheet, is this code correct ?

    Windows(filen).Activate
    If Sheet1.Range("K4:N50").Cells.Value = 0 Then
    Range("A7").Select
    Application.CutCopyMode = False
    OOMD = Sheets("Sheet2").Range("A16").Text
    Range("A16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End If

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dunno
    why don't you try it and see if it works
    if it doesn't work why don't you debug the code to find out
    a) where it went wrong
    b) why it went wrong
    c) how to resolve the issue

    we can probably help with B) & C) but the earlier steps require at least some effort from yourself, and whilst putting in that effort you'll probably learn an awful lot more about Excel in particular but also how to develop good debugging skills which are transferable to any systems development task
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2011
    Posts
    19

    Exclamation looking for empty workbook in a spreadsheet, is this code correct ?

    If Sheet1.Range("k4:n50").Cells.Value = False Then

    the fault is appearing at this line ...

  4. #4
    Join Date
    May 2011
    Posts
    19
    run time error , type mismatch
    If Sheet1.Range("k4:n50").Cells.Value = False Then

    the fault is appearing at this line ...

    Quote Originally Posted by healdem View Post
    I dunno
    why don't you try it and see if it works
    if it doesn't work why don't you debug the code to find out
    a) where it went wrong
    b) why it went wrong
    c) how to resolve the issue

    we can probably help with B) & C) but the earlier steps require at least some effort from yourself, and whilst putting in that effort you'll probably learn an awful lot more about Excel in particular but also how to develop good debugging skills which are transferable to any systems development task
    Last edited by codee240; 07-19-11 at 10:15.

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Sheet1.Range("k4:n50").Cells.Value returns a two dimensional array of variants. In VBA you can't compare all of the elements of an array in one swoop using the = operator.

    First of all, some terminology: workbooks and spreadsheets are the same thing. "Looking for an empty workbook in a spreadsheet" doesn't make sense.


    The core hierachy in the Excel object model is:
    Code:
    Application
          |
    Workbooks        - a collection of all of the open workbooks
          |
    Workbook         - an open workbook 
          |
    Sheets           - a collection of all of the sheets (worksheets, chart sheets, etc) in a workbook
          |
    Worksheet        - a worksheet in a workbook
          |
    Range            - a range on a worksheet
    The best way to check if a range of multiple cells is empty is to use WorksheetFunction.CountA(). However, I am not convinced from the information on the thread so far that you want to check if a range is truly empty (as opposed to blank). Please can you describe in words what you want to do, and then I/we might be able to suggest something?
    Last edited by Colin Legg; 07-19-11 at 17:14.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •