inserting a cell reference in a nested loop

[kims212]

hi

excel 2007, windows 7

I have a list of terms in col a (103 terms) and a list of company names (674 names) in col b and col c, result to be in col e

I want to check each of the terms in column a, to see if it's in the list in col b and if found, copy the company name in col c into col e.

the following code works fine:

Sub find_lcd_in_gm_co_name_list_single()

    For j = 2 To 675
        col_e = "E" & Trim(Str(j))
        Range(col_e).Select
        ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND(R2C1,RC[-3])),""not found"",R2C3)"
        Range(col_e).Select
    Next j
    
End Sub

but when I try to loop down the list of terms in col a against the list of company names in col b, I can't get the absolute references to work:

Sub find_lcd_in_gm_co_name_list_looping()

 Dim col_a As String
 Dim col_b As String
 Dim col_e As String
 Dim i As Integer
 Dim j As Integer
 Dim formula_a As String
 Dim not_found As String
 not_found = "not found"
 
 For i = 2 To 103
    col_a = "A" & Trim(Str(i))
    
    For j = 2 To 675
        col_b = "B" & Trim(Str(j))
        col_c = "C" & Trim(Str(j))
        col_e = "E" & Trim(Str(j))
        
        formula_a = "=if(iserror(find(" & col_a & "," & col_b & "))," & "R2" & "," & col_c & ")"
        Range(col_e).Select
        If ActiveCell.Value = "not found" Or ActiveCell.Value = "" Then
            ActiveCell.FormulaR1C1 = formula_a
            Range(col_e).Select
        End If
    Next j
    
 Next i
 
End Sub

on the first pass, i = 2 and j = 2, when I stop @ the line: If ActiveCell.Value = "not found" Or ActiveCell.Value = "" Then

if I hover over activecell.value it returns activecell.value=empty
if in the immediate window I type ?activecell.value it returns a blank
if I type ?activecell.value = empty, it returns TRUE

if I hover over formula_a it returns formula_a="=if(iserror(find(A2,B2)),R2,C2)"
if in the immediate window I type ?formula_a, it returns =if(iserror(find(A2,B2)),R2,C2)

if I hover over activecell.formular1c1, it returns activecell.formular1c1 = ""
in the immediate window if I type ?activecell.formular1c1 it returns a blank

when I stop @ line end if,

if I hover over activecell.value, it returns activecell.value=0
if in the immediate window I type ?activecell.value it returns 0
if I type ?activecell.value = empty, it returns TRUE

if I hover over formula_a it returns formula_a="=if(iserror(find(A2,B2)),R2,C2)"
if in the immediate window I type ?formula_a, it returns =if(iserror(find(A2,B2)),R2,C2)

if I hover over activecell.formular1c1 it returns activecell.formular1c1="=if(iserror(find(A2,B2)),R2,C2)"
if in the immediate window I type ?activecell.formular1c1 it returns =IF(ISERROR(FIND(A2,B2)),R2,C2)

however:

if I click in cell E2, the value showing in the cell is 0 and the formula is =IF(ISERROR(FIND('A2','B2')),$2:$2,$B:$B)

on the next j loop, where i=2 and j=3, I get the same results replacing B2 with B3 and C2 with C3.

I've tried everything I can think of, all suggestions on the web - though none that deal with looping through a list of values - all manner of recording a macro and editing the vba code.

as you can see, I've created the formula correctly and even assigned the formula to the active cell but the value and formula in the active cell just aren't correct.

I'm now am at wit's end or the end of my capabilities.

any suggestions would be greatly appreciated, or is this just not possible.

thanks
kim

 

[SkipVought]

Please post a REPRESENTATIVE sample of data (maybe 5-6 rows) in your various columns so we can TEST our approch.

Please also state the expected result for each row.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I want to check each of the terms in column a, to see if it's in the list in col b and if found, copy the company name in col c into col e.

therfore in column E
[tt]
E2: =if(isna(match(b2,$A:$A,0)),"",c2)
[/tt]
Does this work on your sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kims212]

Hi Skip

I've attached an excel worksheet with representative data. I thought that would be the clearest illustration.

what I'm trying to do is find out if the companies in column c are anywhere the list of companies in column b. as the names may not be an exact match but may still be the same company, I've reduced the name of the company in column b to the lowest common denominator, i.e. the part of the name that would most likely be in both the gm company name in column c and the full and correct company name in column b.

if the lcd term in column a is in the company name in column c, it's probably a match, so put the company name from column b corresponding to the lcd term in column a, into the result column d.

i.e. column a: agropur
     column b: Agropur Cooperative - the full and correct name for the company Agropur
     column d: agropur is found somewhere in the company name in the first seven companies (rows) in column c so the full and correct company name Agropur
               Cooperative is put into column d as a probable match for the company in column b

     column a: burnbrae
     column b: Burnbrae Farms Ltd. - the full and correct name for the company Burnbrae
     column d: burnbrae is found in the company name in column c in rows 17, 18 and 19 so the full and correct company name Burnbrae Farms Ltd. is put into column d
               as a probable match for the company in column b

I moved the columns around a bit as it seemed to make more sense with the lcd terms next to the full and complete company names and the company in the list from gm beside the possible match; I've changed the code appropriately.

Sub find_lcd_in_gm_co_name_list()

 Dim i As Integer
 Dim j As Integer
 Dim col_e As String
 Dim rc_find As String
 Dim rc_co_name As String
 Dim rc_find_in As String
 Dim formula_a As String
 Dim not_found As String
 not_found = "not found"
 
 For i = 2 To 25
    col_a = "A" & Trim(Str(i))
    cell_rc_abs = "r" & Trim(Str(i)) & "c1"

    For j = 2 To 40
        col_b = "B" & Trim(Str(j))
        col_c = "C" & Trim(Str(j))
        col_e = "E" & Trim(Str(j))
        
        formula_a = "=if(iserror(find(" & col_a & "," & col_c & "))," & "R2" & "," & col_b & ")"
               
        Range(col_e).Select
        If ActiveCell.Value = "not found" Or ActiveCell.Value = empty Then
            ActiveCell.FormulaR1C1 = formula_a
            Range(col_e).Select
       End If
    Next j
 Next i 
End Sub

clear as mud? I'll try to explain it better if you have any questions now that you've seen the data.

thanks very much
kim

 

[Andrzejek]

kim,
"I've attached an excel worksheet with representative data" - well, attachments are sometimes blocked and yours is not available :-(

You can show you data here with [ignore][pre] some data [/pre][/ignore] to preserve alignment

Have fun.

---- Andy

 

[SkipVought]

did that formula work?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kims212]

Hi Skip

alas no, the formula didn't work for me; i got the error: application-defined or object-defined error.

mea culpe, my apology Skip and Andrzejek, I think I forgot to attach the worksheet - par for the course.

in fact, I know that I didn't because now that I'm trying to do it, I have no idea how to attach it; it's on my server, but doesn't have an

http://...

address. is there no way to pick it from my server and attach it that way?

kim

 

[kims212]

I tried to put the data between the preserve format codes you suggested - I couldn't get them to display here, how do you get them to display as you did? - but it didn't keep the columns; not sure if I did something wrong.

but I uploaded the file to a folder on our website and I think that you can get it there:

http://www.gftc.ca/uploads/userfiles/files/100 Processors list - for tektips forum.xls

I pasted the address in my browser url and could save the file in my own folder; I couldn't seem to open it rather than save it, but I could open it once I saved it.

I don't know if this gives you access to it and/or you can read it or not, but it's there.

is there a way to browse my server and attach a file?

thks
kim

 

[PHV]

You wanted simply this ?

Sub find_lcd_in_gm_co_name_list()
Const maxA = 25, maxC = 40
Dim lngA As Long, lngC As Long
For lngC = 2 To maxC
    For lngA = 2 To maxA
        If InStr(1, Cells(lngC, "C").Text, Cells(lngA, "A").Text, vbTextCompare) Then
            Cells(lngC, "E") = Cells(lngA, "B")
            Exit For
        End If
    Next
    If lngA > maxA Then Cells(lngC, "E") = "Not Found"
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kims212]

Hi PHV

yes, that worked very well.

I'm not quite sure why I was attempting to do it using generated a formulae, I suppose because I generated the sub based on a recorded macro and expanded from there. your procedure is much cleaner. forest for the trees etc. ...

just fmi, should it have been possible to generate the formulae in the cells as I was attempting to do? I realize that the single formula worked because there was an absolute cell reference - R2C1 - in the line: ActiveCell.FormulaR1C1 = "=IF(ISERROR(FIND(R2C1,RC[-3])),""not found"",R2C3)" but should it be possible to set the activecell.formular2c1 to what I was intending? and is there a reason that though the formula generated and looked fine in the immediate window, it ended up in the destination cell with strange references and single quotes around the cell references for the find function?

as in:

if I hover over activecell.formular1c1 it returns activecell.formular1c1="=if(iserror(find(A2,B2)),R2,C2)"
if in the immediate window I type ?activecell.formular1c1 it returns =IF(ISERROR(FIND(A2,B2)),R2,C2)

but the formula in the destination cell turned out as: =IF(ISERROR(FIND('A2','B2')),$2:$2,$B:$B)

thanks so much for your assistance
kim