Loop each workbook and update value base on current Workbook value

[KIM Chanthan]

Hi,

I have one issue to loop each workbook and update the data every month.
I have tried to write one code, but it could only update 1 cell by single formula.
So there are 4 cells (For one month), i need to write 4 formula. So going along with this kind of formula, i might need to create 4 * 12 = 48 formula for update one year data. Below is my code:
--------------------------------------
>>> use code - tags <<<

Sub EmbedDATA()

  Set ws = Workbooks("Value File.xlsm").Worksheets(1)
       
    'Open Directory and Listing all File
    directory = ActiveWorkbook.Path & "\"
    Filename = Dir(directory & "*.xlsb")
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    'Extract all File Listing
    Do While Filename <> ""
            ' Open workbook by filename
        Workbooks.Open (directory & Filename)
        Worksheets("INPUT").Select
        Client = Range("B1").Value & Range("A7")
        Spouse = Range("B1").Value & Range("A8")
        ProductA = Range("B1").Value & Range("A9")
        ProductB = Range("B1").Value & Range("A10")
      
        Range("B7") = Application.WorksheetFunction.VLookup(Client, ws.Range("A:O"), 4, 0) * Range("B5").Value
        Range("B8") = Application.WorksheetFunction.VLookup(Spouse, ws.Range("A:O"), 4, 0) * Range("B5").Value
        Range("B9") = Application.WorksheetFunction.VLookup(ProductA, ws.Range("A:O"), 4, 0)
        Range("B10") = Application.WorksheetFunction.VLookup(ProductB, ws.Range("A:O"), 4, 0)
        Workbooks(Filename).Save
        Workbooks(Filename).Close
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
        'Move to next filename
        Filename = Dir
    Loop
End Sub

-----------------------
I also attached my files here. So that you might understand my issue clearly.
I am very basic with VBA.
Thanks,

 

[KIM Chanthan]

I have just found they way to loop columns. And i apply it with my real working. I found it works perfectly with below code.
----------
>>> use code - tags <<<

Sub EmbedDATA()
  Set ws = Workbooks("Value File.xlsm").Worksheets(1)
       
    'Open Directory and Listing all File
    directory = ActiveWorkbook.Path & "\"
    Filename = Dir(directory & "*.xlsb")
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    'Extract all File Listing
    Do While Filename <> ""
            ' Open workbook by filename
        Workbooks.Open (directory & Filename)
        Worksheets("INPUT").Select
        Client = Range("B3").Value & Range("D257").Value & Range("C257").Value
        Spouse = Range("B3").Value & Range("D258").Value & Range("C258").Value
        ProductA = Range("B3").Value & Range("D259")
        ProductB = Range("B3").Value & Range("D260")
For i = 19 To 30

        Cells(257, i) = Application.WorksheetFunction.VLookup(Client, ws.Range("A:p"), i - 14, 0) * Cells(6, i)
        Cells(258, i) = Application.WorksheetFunction.VLookup(Spouse, ws.Range("A:p"), i - 14, 0) * Cells(6, i)
        Cells(259, i) = Application.WorksheetFunction.VLookup(ProductA, ws.Range("A:p"), i - 14, 0)
        Cells(260, i) = Application.WorksheetFunction.VLookup(ProductB, ws.Range("A:p"), i - 14, 0)

Next i
       
        Workbooks(Filename).Save
        Workbooks(Filename).Close
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
        'Move to next filename
        Filename = Dir
    Loop
End Sub