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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,143,677
Messages
5,720,254
Members
422,273
Latest member
linds75

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