Creating an IF block (Pulling data from multiple files to affect macros)

  • I'm trying to create an IF function where if the data on a file opened by a macro matches the data in a given cell (in the workbook that's running the macro), it'll continue with the macro.
    BUT, if it doesn't match the data in the cell, it will close that file and loop to the beginning of the macro (so that it will repeat with the next file)
    My coding is as follows:



    With the important bit being:


    Code
    'Dim PartCode As String
    'PartCode = [{C:\Users\ROB180\Documents\MACRO TESTING.xlsm}.Sheet1.Range("f8")]
    'If Left(strfile, Len(strfile) - 4) + "!R?C?" = PartCode
    'Then [Continue]
    'Else If Left(strfile, Len(strfile) - 4) + "!R?C?" [Doesn't =] PartCode
    '[Close file]
    'strfile = Dir
    'Loop
    'End If


    All of the [,{,}, & ]'s are enclosing the bits that i'm struggling to find the 'code equivalent' to..
    Also, i've used "Left(strfile, Len(strfile) - 4) + "!R?C?" in the coding because i'm trying to refer to a cell in the file it's just opened (but the sheetname will be the same as the filename, so will vary)


    Is there any hope of this working well? If not, could someone suggest a different way to this!
    I'm pretty new to all this, so I know what I want.. But not how to phrase it.

  • Re: Creating an IF block (Pulling data from multiple files to affect macros)


    Your making it difficult - why not get the value before opening the csv files - the value you are comparing from your description is in the workbook running the code so just change the order

    Code
    'get value before opening csv files
    partcode = ActiveWorkbook.Sheets(1).Range("F8")
    'iterate through csv files
    'compare value to partcode

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Creating an IF block (Pulling data from multiple files to affect macros)


    How would I get the values before opening the files?
    And do you mean something like this?


    Code
    strfile = Dir("C:\Users\ROB180\Desktop\EPS16-1\" & wanteddate & "*.CSV")
    'Do checks here?
    'Get Part Code from file
    'If cell data = PartCode, continue
    'If not a match, strfile = dir; loop
    'End if
    Do While strfile <> ""
        Workbooks.Open Filename:="C:\Users\ROB180\Desktop\EPS16-1\" & strfile, ReadOnly:=True
  • Re: Creating an IF block (Pulling data from multiple files to affect macros)


    I think your problem is in

    Code
    'PartCode = [{C:\Users\ROB180\Documents\MACRO TESTING.xlsm}.Sheet1.Range("f8")]


    What I am saying is get this value BEFORE opening the CSV file and you will not need any { or [ syntax - it does not appear this value changes so get this value first then start iterating through the csv files - then just use basic string comparisons to see if there is a match - if you step through this code i think you will find it will generate an error that will not cause the code to error out. I am not talking about getting the value from the CSV file before opening it but the value from the workbook that you place into PartCode

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Creating an IF block (Pulling data from multiple files to affect macros)



    Would that work for the IF block?
    The ("R1C2") refers to the cell which it's checking the value to, and the PartCode's ("f10") is what it compares it to.
    If that'd work, how would I phrase it to *continue* with the rest of the macro?

  • Re: Creating an IF block (Pulling data from multiple files to affect macros)


    Code
    If ("R1C2") = PartCode Then continue
    Else: strfile = Dir
    Loop
    End If


    Just realised the IF/THEN should be like this in the code..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!