Solved

Check for null

Posted on 2013-06-05
2
257 Views
Last Modified: 2013-06-09
I need to skip this step if A2 of the Manual Sheet is null.  Is there a way to do that?

Dim lastCol As Long, lastRow As Long

   
    With Sheets("Manual")
        'lastCol = .Cells(2, .Columns.Count).End(xlToLeft).column
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2", .Cells(lastRow, 1)).Copy
        '.Range("A2", .Cells(Rows.Count, "A")).Copy
       .Range("A2:A300").Copy
    End With

   
    With Sheets("Cus_tmp")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow = 1 And .Cells(1) = "" Then lastRow = 0
        .Cells(lastRow + 1, "A").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
0
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Assisted Solution

by:Shanan212
Shanan212 earned 150 total points
ID: 39222907
if sheets("Manual").range("A2").value <> vbnullstring then

end if

Open in new window

   
Use above: It checks to see if A2 is not equal to null. You can put your code inside the if-statement so that it will only run if A2 is not equal to null.
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 350 total points
ID: 39222920
Hi,

Depending on whether you mean 'Null' or 'Empty'...

If Not(IsEmpty(Worksheets("Manual").Range("A2"))) Then
   With Sheets("Manual")
        'lastCol = .Cells(2, .Columns.Count).End(xlToLeft).column
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A2", .Cells(lastRow, 1)).Copy
        '.Range("A2", .Cells(Rows.Count, "A")).Copy
       .Range("A2:A300").Copy
    End With

   
    With Sheets("Cus_tmp")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow = 1 And .Cells(1) = "" Then lastRow = 0
        .Cells(lastRow + 1, "A").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With 
End If

Open in new window


OR

If Not(IsNull(Worksheets("Manual").Range("A2"))) Then
...
End If

Open in new window



PS. Note I used the Worksheets() collection, not Sheets() like you did.

BFN,

fp.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question