Consulting

Results 1 to 3 of 3

Thread: How to Handle Blank cell value in VBA

  1. #1

    How to Handle Blank cell value in VBA

    I am trying below code in Macros which transpose my data from one sheet to another sheet this working fine but if any of my cell goes empty it throw compile debug error , how can i handle empty cell in below macros

    Error Run Time erro 1004

    Application defined or object defined error

    For x = 2 To LRow

    Test1 = wsRaw.Cells(x, getColumn(wsRaw, "Col1")).Value
    Test2 = wsRaw.Cells(x, getColumn(wsRaw, "Col2")).Value > Error in this column as it goes empty ?


    For col = 1 To 45 Step 2
    If wsRaw.Cells(x, col).Value <> "" Then


    wsAR.Range("C" & sRow).Value = Test1
    wsAR.Range("M" & sRow).Value = Test2


    End If


    Next col
    sRow = sRow + 1

    Next x

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Welcome to the forum!

    What is the value of LRow? Its dimension should be Long.

    What is getColumn? Why its inputs are a worksheet and a string is unclear. IF Col1 is the cell address on worksheet wsRaw, I would think that wsRay.Range("Col1").Column would suffice?

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Fix Function GetColumn so that if (wsRaw, "Col?") is not a valid column number, it returns a column number that is always empty in Row x
    For example in that situation GetColumn = 999.
    This will make Test2 = "".

    Since the issue is obviously with the Function GetColumn, it would be better to share that code.

    If you had put a "Watch" on GetColumn, you would have seen that GetColumn was returning an error code or 0 (zero) whenever the error occurred.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

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
  •