VBA - Switching between generic workbooks

[flipdazed]

Hi there,

I have a background in Fortran 95 programing and I'm trying to grasp the basics of VBA. I have managed to write a small program but I don't have enough VBA knowledge to figure out what is wrong

My problem is as follows:
I have multiple files with a set of questions with the answers "Y" or "N" in set cells. I want to run the same macro from each of these files which will return values of "1" or "0" to a master file with an IF statement.

1) The first part of my problem is identifying the filename of the file I currently open and referencing it in Workbooks() I haven't properly refenced my file name from from the dim rawaudit in the code:

[/I]Workbooks("RawAudit").Sheets("Sheet1").Select

2) The second part of my problem is the IF statement I have. I'm sure it isn't very efficient and it doesn't properly work either but the basics are there.

I've tried to colour in the code below to make it more readable. (The conditional file open part works perfectly as I copied it form the microsoft site)

Thanks a lot,

Alex.

[COLOR=blue]Sub[/COLOR] AuditMasterfile_DataTransfer()
[COLOR=seagreen]' RUN THIS MACRO INSIDE THE RAW AUDIT DATA FILE[/COLOR]
[COLOR=seagreen]' Transfers Data from Raw Audit File by opening MasterFile and transfering data _[/COLOR]
[COLOR=seagreen]into the transfer box. Outputs 1 for a Y and 0 for a No.[/COLOR]
 
[COLOR=seagreen]'Obtain name of Monthly Audit File[/COLOR]
[COLOR=blue]Dim[/COLOR] RawAudit [COLOR=blue]As String[/COLOR]
    RawAudit = ThisWorkbook.Name
 
[COLOR=seagreen]'Open Master File if not currently open - dim from microsoft[/COLOR]
[COLOR=blue]Dim [/COLOR]strFileName [COLOR=blue]As String[/COLOR]
    
[COLOR=seagreen]   ' Full path and name of file.[/COLOR]
    strFileName = "C:\Users\105057819\Documents\Monthly Sales Audit\ITO Audit Masterfile.xlsx"
    [COLOR=seagreen]' Call function to test file lock.[/COLOR]
    [COLOR=blue]If Not[/COLOR] FileLocked(strFileName) [COLOR=blue]Then[/COLOR]
   [COLOR=seagreen]' If the function returns False, open the document.[/COLOR]
    Workbooks.Open strFileName
   [COLOR=blue]End If[/COLOR]
    
[COLOR=seagreen]'Goto Raw Audit File and Extract all data to Masterfile with 1 or 0 depending on Y/N[/COLOR]
    Workbooks("RawAudit").Sheets("Sheet1").Select
 
  [COLOR=seagreen]  'Q1[/COLOR]
    [COLOR=blue]If[/COLOR] Range("B9") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K23") = "1"
    [COLOR=blue]ElseIf [/COLOR]Range("B9") = "N"[COLOR=blue] Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K23") = "0"
    [COLOR=blue]End If[/COLOR]
[COLOR=seagreen]   'Q2[/COLOR]
    [COLOR=blue]If [/COLOR]Range("B10") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K24") = "1"
    [COLOR=blue]ElseIf[/COLOR] Range("B10") = "N" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K24") = "0"
    [COLOR=blue]End If[/COLOR]
 [COLOR=seagreen]   'Q3[/COLOR]
    [COLOR=blue]If[/COLOR] Range("B11") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K25") = "1"
    [COLOR=blue]ElseIf[/COLOR] Range("B11") = "N" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K25") = "0"
[COLOR=blue]   End If[/COLOR]
   [COLOR=seagreen]'Q4[/COLOR]
    [COLOR=blue]If[/COLOR] Range("B12") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K26") = "1"
    [COLOR=blue]ElseIf[/COLOR] Range("B12") = "N" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K26") = "0"
  [COLOR=blue]  End If[/COLOR]
   
[COLOR=blue]End Sub[/COLOR]
 
 
[COLOR=green]'Open Masterfile if not already open - function from microsoft[/COLOR]
[COLOR=blue]Function[/COLOR] FileLocked(strFileName [COLOR=blue]As String[/COLOR]) [COLOR=blue]As Boolean[/COLOR]
   [COLOR=blue]On Error Resume Next[/COLOR]
[COLOR=green]  ' If the file is already opened by another process,[/COLOR]
[COLOR=green]  ' and the specified type of access is not allowed,[/COLOR]
[COLOR=green]  ' the Open operation fails and an error occurs.[/COLOR]
   [COLOR=blue]Open[/COLOR] strFileName [COLOR=blue]For Binary Access Read Write Lock Read Write As[/COLOR] #1
   [COLOR=blue]Close [/COLOR]#1
  [COLOR=green] ' If an error occurs, the document is currently open.[/COLOR]
   [COLOR=blue]If[/COLOR] Err.Number <> 0 [COLOR=blue]Then[/COLOR]
    [COLOR=green]  ' Display the error number and description.[/COLOR]
      [COLOR=blue]MsgBox[/COLOR] "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   [COLOR=blue]End If[/COLOR]
[COLOR=blue]End Function[/COLOR]

 

[RoryA]

Yes, it will. You can get round that by using:

Select Case UCase(wbkRawAudit.Sheets("Sheet1").Range("B" & lngRow).Value)

to convert the checked value to upper case.