Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
INDEX MATCH
I wrote a macro that copies links from coloured cells in a sheet DATABASE in the old release of a given file to a DATABASE (using INDEX / MATCH) in the new file, so the inputs do not need to be re-linked each time a new version of a file comes out.
And the file doesnt work. Any ideas? I am pretty new to VBA, so I am having troubles spotting what is wrong. Function WorkbookIsOpen(wbname) As Boolean 'Returns TRUE if the workbook is open Dim x As Workbook On Error Resume Next Set x = Workbooks(wbname) If Err = 0 Then WorkbookIsOpen = True _ Else WorkbookIsOpen = False End Function Function FileNameOnly(pname) As String Dim i As Integer, length As Integer, temp As String length = Len(pname) temp = "" For i = length To 1 Step -1 If Mid(pname, i, 1) = Application.PathSeparator Then FileNameOnly = temp Exit Function End If temp = Mid(pname, i, 1) & temp Next i FileNameOnly = pname End Function Function Fillcolor(cell) As Integer Fillcolor = ActiveCell.Interior.ColorIndex End Function Sub SimpleUpdate() ' OPEN THE LOCAL FILE WITH LINKS, BUT OLD RELEASE MsgBox "Please select your Old Release file" Dim Filt As String Dim FilterIndex As Integer Dim Title As String Dim FMCFilenameOldRel As String Filt = "Excel Files (*.xls),*.xls," & "All Files (*.*),*.*" FilterIndex = 1 Title = "Select your Financial Monitoring Cycle file" FMCFilenameOldRel = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title) If FMCFilenameOldRel = "False" Then MsgBox "No file was selected. Try again later" Exit Sub End If ' DETERMINING WHETHER CHOSEN FILE IS ALREADY OPEN AND OPENING IF NECESSARY If WorkbookIsOpen(FileNameOnly(FMCFilenameOldRel)) = True Then MsgBox "The File is open. I will copy links from the opened file." Else Application.Calculation = xlCalculationManual Application.CalculateBeforeSave = False Workbooks.Open (FMCFilenameOldRel) End If ' START TRANSFERRING THE DATA Dim aletters, bletters, db As Range Dim k, l As Integer 'IS THE PROBLEM HERE? Workbooks(FileNameOnly(FMCFilenameOldRel)).Workshe ets("DATABASE").Range("j5:j4000").Name = "aletters" Workbooks(FileNameOnly(FMCFilenameOldRel)).Workshe ets("DATABASE").Range("j1:bq1").Name = "bletters" Workbooks(FileNameOnly(FMCFilenameOldRel)).Workshe ets("DATABASE").Range("j5:bq4000").Name = "db" For l = 5 To 4000 For k = 10 To 70 ThisWorkbook.Activate Cells(l, k).Activate If Fillcolor(ActiveCell) = "-4142" Then Else Cells(l, k).Formula = _ Application.Index(Range("db"), _ 'WHAT IS WRONG HERE? Application.Match(Cells(l, 9).Value, Range("bletters"), False), _ Application.Match(Cells(1, k).Value, Range("bletters"), False)).Formula End If Next k Next l End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |