Need Help adding to this macro so it finds the cell closest to 20 and returns that value?

[tonywatsonhelp]

Hi Everyone,
I hope this isn't going to sound to complicated as I really need your help on this one?

I have this macro that opens every file in a folder and copies cell values into my spreadsheet, this works perfectly,

However as well as what I've set it up to do, I also need it to find the cell in column A with the value closest to "20" and put this into my excel spreadsheet column C and the closest to ""25" and put this in Column D

I'm really stuck and need this done for a report, please help me if you can?

Basically I'm just looking for the bit of code I'd add to this macro that will make it also find the cell closest to the number 20 and the cell closest to the number 25

Option Explicit


Const FOLDER_PATH = "C:\Users\iplayer\Documents\Odesk New1\41 Odeskrune Hansen mcro\Patient 47\"  'REMEMBER END BACKSLASH



Sub ImportWorksheets()
   '=============================================
   'Process all Excel files in specified folder
   '=============================================
   Dim sFile As String           'file to process
   Dim wsTarget As Worksheet
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim rowTarget As Long         'output row
   
   rowTarget = Range("A65536").End(xlUp).Offset(1, 0).Row
   
   'check the folder exists

   
   'reset application settings in event of error
   On Error GoTo errHandler
   Application.ScreenUpdating = False
   
   'set up the target worksheet
   Set wsTarget = Sheets("Sheet2")
   
   'loop through the Excel files in the folder
   sFile = Dir(FOLDER_PATH & "*.xls*")
   Do Until sFile = ""
      
      'open the source file and set the source worksheet - ASSUMED WORKSHEET(1)
      Set wbSource = Workbooks.Open(FOLDER_PATH & sFile)
      Set wsSource = wbSource.Worksheets(1) 'EDIT IF NECESSARY
      
      'import the data
      With wsTarget
         .Range("A" & rowTarget).Value = wsSource.Range("A2").Value
         .Range("B" & rowTarget).Value = wsSource.Range("B2").Value 'ok
         .Range("Z" & rowTarget).Value = wsSource.Range("A16").Value 'ok
         .Range("R" & rowTarget).Value = wsSource.Range("A20").Value 'ok
         .Range("S" & rowTarget).Value = wsSource.Range("B20").Value 'ok
         .Range("T" & rowTarget).Value = wsSource.Range("A25").Value 'ok
         .Range("U" & rowTarget).Value = wsSource.Range("B25").Value
         .Range("V" & rowTarget).Value = wsSource.Range("A27").Value
         .Range("W" & rowTarget).Value = wsSource.Range("B27").Value
        .Range("AA" & rowTarget).Value = wsSource.Range("B16").Value

      End With
      
      'close the source workbook, increment the output row and get the next file
      wbSource.Close SaveChanges:=False
      rowTarget = rowTarget + 1
      sFile = Dir()
   Loop
   
errHandler:
   On Error Resume Next
   Application.ScreenUpdating = True
   
   'tidy up
   Set wsSource = Nothing
   Set wbSource = Nothing
   Set wsTarget = Nothing
End Sub

Thanks

Tony

 

[mikerickson]

There is no need for a macro, this CSE formula will return the value in A1:A14 closest to 20.

=INDEX(A1:A14, MATCH(MIN(ABS(A1:A14-20)), ABS(A1:A14-20), 0), 1)

 

[tonywatsonhelp]

thanks for your help mikerickson, a formula would be fine but this doesn't work for some reason?

I had to edit it as my data is much larger but I put:

=INDEX(A34:A150000, MATCH(MIN(ABS(A34:A150000-20)), ABS(A34:A150000-20), 0), 1)

all I get is error?

any idea why?

Tony

 

[Mike LH]

Hi,

Read this

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[tonywatsonhelp]

HI Mike LH,
Thank you, you are correct just tested it works perfectly Big Thanks

Tony