+ Reply to Thread
Results 1 to 3 of 3

Pull the G:G column values depends upon status of Awaiting for FS action IN AV:AV vba

Hybrid View

johnodys Pull the G:G column values... 06-09-2014, 08:30 AM
Tinbendr Re: Pull the G:G column... 06-10-2014, 12:58 PM
johnodys Re: Pull the G:G column... 06-16-2014, 02:18 AM
  1. #1
    Forum Contributor
    Join Date
    07-23-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    143

    Pull the G:G column values depends upon status of Awaiting for FS action IN AV:AV vba

    Dear Expert

    I Need help with vba I have master file saved at path.. and i want pull particular column values in G:G SO_NBR depends upon towards "Awaiting for FS action" ,

    Three steps Need to follow
    first remove duplicates through SO_NBR Then filter only "Awaiting for FS action" of "AV:AV" At last
    Take SO_NBR 10Digit NOs value paste into in L:L column in current workbook..

    Find the attachment
    Attached Files Attached Files

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: Pull the G:G column values depends upon status of Awaiting for FS action IN AV:AV vba

    Put in a Module.

    Sub PullFS()
    Dim WS As Worksheet
    Dim WB_M As Workbook
    Dim WS_M As Worksheet
    Dim Unique As New Collection
    Dim A As Long
    Dim LR_s As Long
    Dim LR_m As Long
    Dim FN As Variant
    Dim Ctr As Long
    
    Ctr = 2
    Set WS = Workbooks("Pull_FS ACTION.xlsb").Worksheets("ODR")
    
    'Browse for file
    FN = Application.GetOpenFilename("Excel Files (*.xls?), *.xls?")
    
    'Open workbook
    Set WB_M = Workbooks.Open(FN)
    'Set WB_M = Workbooks("Master_AFS.xlsx")
    
    'Clear range on FS worksheet
    WS.Range("L:L").Clear
    
    'Define masterworkbook sheet1
    Set WS_M = WB_M.Worksheets(1)
    
    With WS_M
        'Last row of master
        LR_m = .Cells(.Rows.Count, "G").End(xlUp).Row
    
        'Iterate Column G and pick unique values.
        For A = 2 To LR_m
            If .Range("G" & A) <> "" Then
                On Error Resume Next
                Unique.Add CStr(.Range("G" & A)), CStr(.Range("G" & A))
                On Error GoTo 0
            End If
        Next
    
        'Iterate unique list to search from.
        For A = 1 To Unique.Count
            With .Range("G2:G" & LR_m)
            'Debug.Print Unique(A)
            Set c = .Find(Unique(A), LookIn:=xlValues)
            If Not c Is Nothing Then
                Do
                    'Test for F5 value
                    If c.Offset(, Range("AV1").Column - Range("G1").Column) = "Awaiting for FS action" Then
                        'Output to F5 sheet.
                        WS.Range("L" & Ctr) = c
                        'Increment row counter
                        Ctr = Ctr + 1
                        'Stop looking for this item.
                        Exit Do
                    Set c = .FindNext(c)
                    End If
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
            End With
        Next
    End With
    'Close workbook.
    WB_M.Close False
    End Sub
    David
    (*) Reputation points appreciated.

  3. #3
    Forum Contributor
    Join Date
    07-23-2013
    Location
    bangalore
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    143

    Re: Pull the G:G column values depends upon status of Awaiting for FS action IN AV:AV vba

    Thanks alot Works Perfectly...But It alot of time for completion??

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. need excel formula to get result from data table depends upon week,partner,column values
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2013, 08:41 AM
  2. [SOLVED] combobox values depends on cell status.
    By rydysh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-07-2013, 10:36 AM
  3. [SOLVED] VBA pull data from closed workbook depends upon header status !!! need help of experts
    By johnodys in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2013, 07:06 AM
  4. [SOLVED] Error message in formulas awaiting values.
    By mamann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-04-2013, 12:25 PM
  5. Replies: 4
    Last Post: 04-08-2012, 09:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1