1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vba Code-Lookup for Cell A2 value-In Another File-In CellB2 -Result become CurrenFile Save As Name

Discussion in 'VBA Macros' started by Chirag R Raval, Jun 19, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    Subject :- Vba Code help Require -Lookup for Cell A2's value-In Another File-In Column "A" -GET Column "B" ' S VALUE As Result ..which become Current File's Save As Name ..

    I have multi file to save as via vba code.


    New file Added (Generated) as process of running VBA code ..

    (code will be generate many files)

    newly created file's cell "A2" has Buyer Number..
    need to save this newly created file as this "A2"'s buyer number's Name which found on another Buyer Master file on Column "B" (Buyer Master's Number column (Lookup Columns) is same .."A"

    Need to code that vlookup (Hidden Mode, Closed file-without open buyer master) on search for this file's A2's value in another file...in Columns "A" & get B2's Value (name) ...so I can make this result as New File Save As name.

    Can anyone help regarding this situation?

    Regards,

    Chirag Raval
    Last edited: Jun 19, 2017
  2. Marc L

    Marc L Excel Ninja

    Messages:
    3,174

    Hi !

    As VLookup worksheet formula works on a closed worbook …
    Chirag R Raval likes this.
  3. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    thanks for ...Very Fast.... lightening speed...reply...
    but how...? without open buyer master?.

    Regards,
    Chirag Raval..
  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,174
    At very beginner level, just try !

    • First, open both workbooks.
    • In a cell, enter manually the VLOOKUP formula.
    • Once formula is correct, close the "Buyer Master" file.
    • Now just read the updated formula : you have the correct syntax
    for a VLookup formula on a closed workbook whatever manually or by code …
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear sir mark L

    Thanks for your valuable guide ..
    My office closed ..I am employee
    I will try & return what I get soon tomorrow
    Thanks again
    Regards.
    Chirag Raval
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    I declare variable that hold the result of vlookup to use that result as new file name.

    Dim Myname As string

    Myname = Application.WorksheetFunction.vlookup(range("A2").Value, '[SUITING-BUYER MASTER.xlsx], BUY MASTER'!$H:$I,2,false)

    but no result Syntext Error Shown...

    Regards,
    Chirag Raval
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    sorry for above trial code that adopt from other we site..that not work..

    I get below code as per your above suggestion..


    Dim Myname As string

    Myname =Application.WorksheetFunction:= VLOOKUP(A1,'C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!$H:$I,2,FALSE)

    that displayed "Syntex Error"

    Please help.

    Regards,
    Chirag Raval
  8. Marc L

    Marc L Excel Ninja

    Messages:
    3,174
    Bad syntax without double quotes for a VBA string !​
    Code (vb):
    V = Application.VLookup([A2].Value, "'C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!$H:$I", 2, False)
    In case of a formula result error, variable must be a Variant
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    after check variable Myname as variant & string ,

    error 2015 displayed in immediate window

    Code (vb):

    Sub test_of_vlookup()

    Dim myname As Variant

    myname = Application.vlookup([A2].Value, "'C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!$H:$I", 2, False)

    Debug.Print myname

    End Sub
     
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    I already use this code in my original macro ...file generated.... but it stop after generate 1st file & wait for saving process ..no further steps taken by code..

    before this name issue, ...its successfully generated ...& saved in fixed path .

    I attached my original files with code for your reference (in bult with your suggested code ) & also attach buyer master for source buyer name..

    hoe-your co-operations..

    Regards,
    Chirag Raval

    Attached Files:

  11. Marc L

    Marc L Excel Ninja

    Messages:
    3,174
    So just use a cell :​
    Code (vb):
    Sub Test1()
            Dim S$
        With [C4]
            .Formula = "=VLOOKUP(A2,'C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!$H:$I,2,FALSE)"
            S = .Text
            .Value = ""
        End With
            Debug.Print S
    End Sub
    No issue on my side …
  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    Amazing...Great Work..Great Concept...that never seen before...

    so there are

    (1) first need to result store in any Unused - Unusable cell of same sheet..(far on sheet..Not Part Of processing range)

    (2) now Catching.. Trapping-Locking..Holding...that cell .. Through "With ..statement .........As an Object..

    (3) Insert any formula in that cell.....

    (4) instantly pass that result to variable as a static -general converted text so variable hold only result not formula (s=.text)

    (5) now variable holding required result value

    (6) Now instantly make that cell blank..null value (.value="")

    (7) Now release that cell object "End With"

    (8) & then use that variable anywhere in code for any requirement...


    Really sir, you teach me amazing concept.....(and also readers for this thread...) & also thank for help to complete my whole process.

    now I can save my code generated new file with "Name" retrieve- result through vlookup on closed file ..

    I always be thankful to you for help many time when I stop somewhere in many code...you are really amazing..

    Regards,

    Chirag Raval
    Last edited: Jun 20, 2017
  13. Marc L

    Marc L Excel Ninja

    Messages:
    3,174

    For the step (4) it may be also .Value property
    as .Text is the string display of the cell.

    As you created a duplicate thread for exactly same subject,
    this one is finished …
    Chirag R Raval likes this.
  14. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    Thanks for your prompt reply...& drop more light on the subject..to understand things..

    sadly sorry but...actually I not aware when start this thread ..that this thread also goes towards "variable Hold the result...of formula" to achieve just file name via vlookup.. & I start new thread suddenly "how Variable Hold the result of formula" ....that unfortunately meet this thread...please believe ..that's co incidence ..& not knowingly try for generate Duplicate thread...

    any way ..I also believe that this thread should be close here ..because
    for which this thread start .. meet with the requirement...

    again thanks for your co-operations...& your valuable guidance..
    & hope for your little help in future...

    Regards,
    Chirag Raval
  15. Chirag R Raval

    Chirag R Raval Member

    Messages:
    326
    Dear Sir,

    Below is part of whole code from above attached order status file... that save newly generated file naming based on A2. value for that it will temp open another excel file vlookup & close...this is for
    loops first run process ...there are many files will be generate...
    each time for vlookup this file for buyer name..(buyer-master) temp open & close...

    how to programmed that if file once open by code...& it will constant open in background till all new files saved on fixed path...if code found there are no file pending for save..its closed on that time..

    Code (vb):

    myname = Application.VLookup([A2].Value, "'C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!$H:$I", 2, False)
           
        fPath = "C:\Documents and Settings\area25-2\Desktop"
        fName = Sheets("Sheet1").Range("A2").Text & myname & " OPEN ORDER STATUS AS ON  "
        ActiveWorkbook.SaveAs Filename:=fPath & "\" & fName & Format(Date, "DD-MM-YY") & ".xlsx"
           
        ActiveWorkbook.Close
                               
                  Next i
            End With
        End With
        Set swb = Nothing
        Set acsht = Nothing

     
    hope your little help..

    Regards

    Chirag Raval..

Share This Page