Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match
I am attempting to use VBA script to match two columns of data.
My problem is not the actual formula because I can do that, however; since the number of rows changes on a daily basis I don't know how to work in that variable in the formula I have used =ISNA(MATCH(A2,$J$2:$J$15125)) and since it is a double comparison =ISNA(MATCH(J2,$A$2:$A$14753)) And then copied the formulas down in there respective columns until the last entry. Now my issue... This is for a report that is run on a daily basis and the range changes on a daily basis. So Column A may go from A2~A14200 one day and A2~A13725 the next and Column J may go in a simular fashion. Can a macro be written that will automatically detect the size of each set of data Columns A and J, write the formula down in the worksheet and copy it for as far as necessary. Thanks for your assistance. Christopher |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match
Instead of hard-coding your range references into the formula, create a
dynamic named range. Debra Dalgleish has instructions he http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ... I am attempting to use VBA script to match two columns of data. My problem is not the actual formula because I can do that, however; since the number of rows changes on a daily basis I don't know how to work in that variable in the formula I have used =ISNA(MATCH(A2,$J$2:$J$15125)) and since it is a double comparison =ISNA(MATCH(J2,$A$2:$A$14753)) And then copied the formulas down in there respective columns until the last entry. Now my issue... This is for a report that is run on a daily basis and the range changes on a daily basis. So Column A may go from A2~A14200 one day and A2~A13725 the next and Column J may go in a simular fashion. Can a macro be written that will automatically detect the size of each set of data Columns A and J, write the formula down in the worksheet and copy it for as far as necessary. Thanks for your assistance. Christopher |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match
On Dec 1, 4:58 pm, "RagDyeR" wrote:
Instead of hard-coding your range references into the formula, create a dynamic named range. Debra Dalgleish has instructions he http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ... I am attempting to use VBA script to match two columns of data. My problem is not the actual formula because I can do that, however; since the number of rows changes on a daily basis I don't know how to work in that variable in the formula I have used =ISNA(MATCH(A2,$J$2:$J$15125)) and since it is a double comparison =ISNA(MATCH(J2,$A$2:$A$14753)) And then copied the formulas down in there respective columns until the last entry. Now my issue... This is for a report that is run on a daily basis and the range changes on a daily basis. So Column A may go from A2~A14200 one day and A2~A13725 the next and Column J may go in a simular fashion. Can a macro be written that will automatically detect the size of each set of data Columns A and J, write the formula down in the worksheet and copy it for as far as necessary. Thanks for your assistance. Christopher Thank you very much I'll give it a try. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... On Dec 1, 4:58 pm, "RagDyeR" wrote: Instead of hard-coding your range references into the formula, create a dynamic named range. Debra Dalgleish has instructions he http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ... I am attempting to use VBA script to match two columns of data. My problem is not the actual formula because I can do that, however; since the number of rows changes on a daily basis I don't know how to work in that variable in the formula I have used =ISNA(MATCH(A2,$J$2:$J$15125)) and since it is a double comparison =ISNA(MATCH(J2,$A$2:$A$14753)) And then copied the formulas down in there respective columns until the last entry. Now my issue... This is for a report that is run on a daily basis and the range changes on a daily basis. So Column A may go from A2~A14200 one day and A2~A13725 the next and Column J may go in a simular fashion. Can a macro be written that will automatically detect the size of each set of data Columns A and J, write the formula down in the worksheet and copy it for as far as necessary. Thanks for your assistance. Christopher Thank you very much I'll give it a try. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Match
RD,
I tried your suggestion and it worked exceptionally well, I just had to modify the code a little bit to work with me since I had one workbook that has all the macro's I need in them I had to actually write the macro to create the name range to use with the match formula but it worked great! Now my worksheet is userfriendly and idiot proof (well as much as can be possible). :-) Macro(s) Follows: Sub Macro_for_Match_Formula() ' ' Macro for Match Formula Testing. ' Macro recorded 12/8/2007 ' ' Keyboard Shortcut: Ctrl+Shift+I ' ActiveWorkbook.Names.Add Name:="NAMEX", RefersToR1C1:= _ "=OFFSET(Sheet1!R2C2,0,0,CountA(Sheet1!C2),1)" ActiveWorkbook.Names.Add Name:="NAMEY", RefersToR1C1:= _ "=OFFSET(Sheet1!R2C10,0,0,CountA(Sheet1!C10),1 )" Application.Run "Match_X_to_Y_Formula_NAMEX" Application.Run "Match_X_to_Y_Formula_NAMEY" End Sub Sub Match_X_to_Y_Formula_NAMEX() ' ' Places the Match Formula into the Compare X TO Y Worksheet Part 1 ' Macro recorded 12/08/2007 ' ' Keyboard Shortcut: Ctrl+Shift+J ' 'We Make sure Compare X TO Y file is active Windows("Compare X TO Y.xls").Activate 'We use the ActiveSheet With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Place the Value "Missing?" in F1 to make Formula start at position F2 Range("F1").Select ActiveCell.FormulaR1C1 = "Missing?" 'We loop from Firstrow to Lastrow (top to bottom) For Frow = Firstrow To Lastrow Step 1 'We check the values in the F column With .Cells(Frow, "F") If Not IsError(.Value) Then If .Value = "" Then .Value = "=ISNA(MATCH(RC[-4],LMS))" 'This will copy the Match formula needed for the Validation Comparison 'in Column F, case sensitive. End If End With Next Frow End With End Sub Sub Match_X_to_Y_Formula_NAMEY() ' ' Places the Match Formula into the Compare X TO Y Worksheet Part 2 ' Macro recorded 12/08/2007 ' ' Keyboard Shortcut: Ctrl+Shift+K ' 'We Make sure Compare X TO Y file is active Windows("Compare X TO Y.xls").Activate 'We use the ActiveSheet With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'Place the Value "Missing?" in M1 to make Formula start at position M2 Range("M1").Select ActiveCell.FormulaR1C1 = "Missing?" 'We loop from Firstrow to Lastrow (top to bottom) For Frow = Firstrow To Lastrow Step 1 'We check the values in the F column With .Cells(Frow, "M") If Not IsError(.Value) Then If .Value = "" Then .Value = "=ISNA(MATCH(RC[-3],LMS))" 'This will copy the Match formula needed for the Validation Comparison 'in Column M, case sensitive. End If End With Next Frow End With End Sub On Dec 3, 3:25 pm, "RagDyer" wrote: Appreciate the feed-back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! wrote in message ... On Dec 1, 4:58 pm, "RagDyeR" wrote: Instead of hard-coding your range references into the formula, create a dynamic named range. Debra Dalgleish has instructions he http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === wrote in message ... I am attempting to use VBA script to match two columns of data. My problem is not the actual formula because I can do that, however; since the number of rows changes on a daily basis I don't know how to work in that variable in the formula I have used =ISNA(MATCH(A2,$J$2:$J$15125)) and since it is a double comparison =ISNA(MATCH(J2,$A$2:$A$14753)) And then copied the formulas down in there respective columns until the last entry. Now my issue... This is for a report that is run on a daily basis and the range changes on a daily basis. So Column A may go from A2~A14200 one day and A2~A13725 the next and Column J may go in a simular fashion. Can a macro be written that will automatically detect the size of each set of data Columns A and J, write the formula down in the worksheet and copy it for as far as necessary. Thanks for your assistance. Christopher Thank you very much I'll give it a try.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | 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 |