none
Excel VBA evaluate if Cell in column D is empty and Cell in column is not empty RRS feed

  • Question

  • Hi,

    I have a requirements in vba excel to evaluate if cell value in column D is empty and if cell in column A has a value then i need to fill up the column D with a string 'IN-TRANSIT". May I ask your assistance on how to do this in vba macro. thank you in advance.

    sample data

    Column A--------B------C----D----E------F
    Transaction#---Label--Qty--ETA--Model--Status
    ---------------------------------------------
    8840863172-----IPH1---5---31-Aug--mm--RECEIVED
    8840863172-----IPH2---5---31-aug--mm--RECIEVED
    8840863216-----IPH1---2---     ---XX-- 
    8840863216-----IPH2---2---     ---xx--  
    8840863216-----IPH3---2--      ---ll-- 
    8840863216-----IPH4---3--      ---ll-- 
    8840863216-----IPH5---3--      ---ll-- 
    8840863216-----IPH6---4--      ---ll-- 
    
    
    RESULT
    
    
    Column A--------B------C----D----E------F
    Transaction#---Label--Qty--ETA--Model--Status
    ---------------------------------------------
    8840863172-----IPH1---5---31-Aug--mm--RECEIVED
    8840863172-----IPH2---5---31-aug--mm--RECIEVED
    8840863216-----IPH1---2---     ---XX--INTRANSIT 
    8840863216-----IPH2---2---     ---xx--INTRANSIT  
    8840863216-----IPH3---2---     --ll--INTRANSIT 
    8840863216-----IPH4---3---     --ll--INTRANSIT 
    8840863216-----IPH5---3---     --ll--INTRANSIT 
    8840863216-----IPH6---4---     --ll--INTRANSIT 
    

    Tuesday, September 2, 2014 3:03 AM

Answers

  • Hi,

    You can try the code below. In the code above, you need to set the variable "rng" as "A1:F9" or "A2:F9" instead of "A2:A9", since range "A2:A9" only has one field and you will get an error message when filtering with "Field:=4".

    Sub Test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws
        Set rng = ws.Range("A1:F" & ws.Cells(Rows.Count, 1).End(xlUp).Row)
        rng.AutoFilter Field:=4, Criteria1:="="
        lrow = .Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("F2:F" & lrow).Value = "IN TRANSIT"
    End With
    rng.AutoFilter 'remove the auto filter
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Thursday, September 4, 2014 12:21 AM
    Wednesday, September 3, 2014 3:14 AM
    Moderator

All replies

  • I tried this approach and so far its working. not yet tested with actual data.

        With ws
            Set rng = ws.Range("A2:A" & ws.Cells(Rows.count, 1).End(xlUp).Row)
            rng.AutoFilter Field:=4, Criteria1:="="
            lrow = .Range("A" & Rows.count).End(xlUp).Row
            ws.Range("F2:F" & lrow).Value = "IN TRANSIT"
        End With


    Tuesday, September 2, 2014 8:42 AM
  • Hi,

    You can try the code below. In the code above, you need to set the variable "rng" as "A1:F9" or "A2:F9" instead of "A2:A9", since range "A2:A9" only has one field and you will get an error message when filtering with "Field:=4".

    Sub Test()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    With ws
        Set rng = ws.Range("A1:F" & ws.Cells(Rows.Count, 1).End(xlUp).Row)
        rng.AutoFilter Field:=4, Criteria1:="="
        lrow = .Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("F2:F" & lrow).Value = "IN TRANSIT"
    End With
    rng.AutoFilter 'remove the auto filter
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Lenoj Thursday, September 4, 2014 12:21 AM
    Wednesday, September 3, 2014 3:14 AM
    Moderator
  • Thank you very much Luna Zhang. Btw, I have another Query this is also related to this thread. I would like to get the string from the column B (Label) which is the model name. May I ask your assistance on how to get this using VBA macro or formula. thank you in advance.

    Do I need to created a separated thread to this query?

    Column A--------B------ Transaction#---Label-- ---------------------- 8840863172-----ALV3000430iPH5S32SIL 8840863172-----DIR300XWHT 8840863216-----SALV3003110REVERE3 8840863216-----FALV3000460iPH5S64GLD 8840863216-----ALV3003110VS985 8840863216-----PP3000440iPH5C16BLU 8840863219-----SALV3003110LUCID3 8840863219-----SALV3003110VS985

    Desired Result Column D--------------------E -----------F Label --------------Label1-------Label2 -------------------------------- ALV3000430iPH5S32SIL-----iPH5S32SIL--iPH5S32SIL DIR300XWHT---------------XWHT---------XWHT

    SALV3003110REVERE3-------REVERE3 ----REVERE3 FALV3000460iPH5S64GLD----iPH5S64GLD--iPH5S64GLD ALV3003110VS985 ---------VS985 ------VS985 PP3000440iPH5C16BLU------iPH5C16BLU--iPH5C16BLU SALV3003110LUCID3--------LUCID3------LUCID3 SALV3003110VS985---------VS985-------VS985

       With ws1
            lrow = .Range("A" & Rows.count).End(xlUp).Row
            Set CopyRng = .Range("A2:A" & lrow)
            CopyRng.Copy Destination:=ws.Range("D" & Rows.count).End(xlUp).Offset(1, 0)
             ? Get the model name from label
       End With






    • Edited by Lenoj Thursday, September 4, 2014 1:38 AM
    Thursday, September 4, 2014 12:35 AM
  • Hi,

    Since the original issue of this thread has been resolved, for more questions, in order to involve more partners of this community to share their knowledge or learn from your interaction with us, I suggest you posting a new thread in this forum.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 4, 2014 1:22 AM
    Moderator
  • Thank you
    Thursday, September 4, 2014 1:48 AM