Reference a Cell within a Named Range

[colmguckian]

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

 

[Jerry Sullivan]

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:

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]

Thanks Jerry, That does exactly what I need
Also thanks for the advice on the Column Offset.