Help

I am just learning VBA for work, I am trying to make some reporting less time consuming.

This is the code:

Dim GRR_GL_T22_1st_Cash As Variant
 
While (file <> "")
      If InStr(file, "GL") > 0 Then
         'error checking to make sure it finds the GL report
         MsgBox "found " & file
 
GRR_GL_T22_1st_Cash = "='H:\Testing\[Gl Report.xlsm]SiteFAIStatus'!$M$17"
   
    'link to worksheet
    With ThisWorkbook.Worksheets(1).Range("H5") '<< change as required
    .Formula = GRR_GL_T22_1st_Cash
    'convert formula to text
    .Value = .Value
Questions:
1. I have found the file that I want, via the while loop. How do I use the variable file instead of 'H:\Testing\[GL Report.xlsm]? That way I can change the file name with ease and not have to copy and paste the file name in several places? I am currently copying the same line to grab multiple data in one line
2. How do I grab field N17 from SiteFAIStatus to thisworkbook.worksheet(1).Range(“I5”) other than copying the whole code above again
3. I am trying to do a IF then else, to give a value of “n/a” when the value from GL Report is blank or O (Number zero) but it is not working.


1. VBA for Applications 7.1
2. What you want it to do: see above
3. Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand
4. Error messages if any: None


Thanks in advance. You people are amazing. I have learned a lot just searching so far.

Vince