VB Script Formula problem

Posted on 2010-01-04
Last Modified: 2012-05-08
Is there a way to change these so that if the Source Cell is empty that they either does nothing or places a vbNullString ? Currently it places a space when there is nothing there.

      DestSheet.Range("I10") = SourceSheet.Cells(0 + Student, 1) & " " & SourceSheet.Cells(0 + Student, 2)
      DestSheet.Range("J11") = SourceSheet.Cells(0 + Student, 2)

Question by:Lazarus
    LVL 50

    Expert Comment

    by:Dave Brett
    You can test for a string greater than 0 characters in the source cell, ie this code will do nothing if  SourceSheet.Cells(0 + Student, 2).Value) is empty


    If Len(SourceSheet.Cells(0 + Student, 2).Value) > 0 Then DestSheet.Range("J11") = SourceSheet.Cells(0 + Student, 2)

    Open in new window

    LVL 85

    Accepted Solution

    You could use:

    DestSheet.Range("I10") = Trim(SourceSheet.Cells(0 + Student, 1) & " " & SourceSheet.Cells(0 + Student, 2))
    LVL 20

    Author Closing Comment

    This solution works best for the script I'm working with, when using the If Len script is tends to skip certain fields in the worksheet and is also longer.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now