Using Match

[elky.man]

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

 

[RagDyeR]

Instead of hard-coding your range references into the formula, create a
dynamic named range.

Debra Dalgleish has instructions here:

http://www.contextures.com/xlNames01.html#Dynamic

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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

 

[elky.man]

Instead of hard-coding your range references into the formula, create a
dynamic named range.

Debra Dalgleish has instructions here:

http://www.contextures.com/xlNames01.html#Dynamic

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


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.

 

[RagDyer]

Appreciate the feed-back.

 

[elky.man]

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