Reference a Cell within a Named Range

colmguckian

New Member
Joined
Feb 11, 2011
Messages
35
I have Macro which uses a Function Called FuzzyMatchByWord Which Matches the String in Cell R6 with a Similar string in Column A Sheet1. The MacroThen Creates a Range Called Range1 starting with the found String in ColumnA an includes the 15 Cells Below. The Next Part is the Part I am Having Difficulty With. I have highlighted it in Orange Below.
I want it to perform another FuzzyMatchByWord to Match the String in R7
With a Similar string in Range1. The Problem is that it does not concentrate its match in Range1 Only Instead in Look in all of Column A
Any Ideas?

Private Sub TextBox2_Change()
Range("R6").Value = TextBox1.Value
Range("R7").Value = TextBox2.Value
Dim I As Integer, DFuzzyMatchByWord As Double, SfuzzyMatchByWord As Double
For I = 1 To 500
DFuzzyMatchByWord = FuzzyMatchByWord((Range("R6:R6").Value), Range("A" & I).Value)
If DFuzzyMatchByWord > 80 Then
Range("A" & I, Columns(1).SpecialCells(xlCellTypeBlanks).Range("A" & I + 15)).Name = "Range1"
End If
Next I
With Range("Range1")
For I = 1 To 500
DFuzzyMatchByWord = FuzzyMatchByWord((Range("R7:R7").Value), Range("A" & I).Value)
If DFuzzyMatchByWord > 90 Then
TextBox3.Value = Range("A" & I).Offset(, 2).Value

End If
Next I
End With
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hello,

If you want to process each cell in Range1, an easy way to do that is using a for each....next statement like this:

Rich (BB code):
Dim c As Range
For Each c In Range("Range1")
    DFuzzyMatchByWord = FuzzyMatchByWord(Range("R7").value, c.value)
    If DFuzzyMatchByWord > 90 Then
        'your code relative to cell c
    End If
Next c

Also note that when using the reference: Range.Offset(RowOffset,ColumnOffset)
you should use .offset(0,2) instead of .offset(,2).
You were probably thinking of .resize( ,2) which means don't resize the Rows, but that doesn't work the same for .offset.
 

colmguckian

New Member
Joined
Feb 11, 2011
Messages
35
Thanks Jerry, That does exactly what I need
Also thanks for the advice on the Column Offset.
 

Forum statistics

Threads
1,141,849
Messages
5,708,973
Members
421,602
Latest member
jkpce1880

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top