• 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...

  • 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

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:
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 …
 
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
 
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
 
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
 
Bad syntax without double quotes for a VBA string !​
Code:
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
 
Dear Sir,

after check variable Myname as variant & string ,

error 2015 displayed in immediate window

Code:
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
 
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
 

Attachments

  • copy 06 -ORDERED STATUS FOR SUITING as on 01.12.2016 to 30.06.2017.xlsm
    546.1 KB · Views: 5
  • SUITING-BUYER MASTER.xlsx
    42.4 KB · Views: 4
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:

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 …
 
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
 
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:
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..
 
Back
Top