search for value in excel worksheet (1 Viewer)

abenitez77

Registered User.
Local time
Today, 14:29
Joined
Apr 29, 2010
Messages
141
My search below only finds 1 value. It does not stop at the other value it should be finding. The value in the cell is identical to the one it does find. What am I doing wrong?

Code:
for vrow = 12 to 15
    fDesc = loWorkSheet.Range("A" & cstr(vrow()).value)
    If fDesc = "" then goto NextvRow
    
    With loWorkSheet.Range("B27:B40")
        set c = .Find(fDesc)
        If Not c Is Nothing then
            loWorkSheet.Range("E" & Cstr(vRow)).Value = loWorksheet.Range("E" & Cstr(vRow)).Value & firstAddress
        End if
    End With
NextvRow:
  Next vRow
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:29
Joined
May 7, 2009
Messages
19,169
Code:
Dim rng As Range
For vrow = 12 To 15
    fDesc = loWorksheet.Range("A" & vrow).Value2 & ""
    If fDesc = "" Then GoTo NextvRow
    
    For Each rng In loWorksheet.Range("B27:B40")
        'Set c = .Find(fDesc)
        If InStr(1, rng.Value2 & "", fDesc, vbTextCompare) <> 0 Then
        'If Not c Is Nothing Then
            'Debug.Print rng.Value2
            loWorksheet.Range("E" & CStr(vrow)).Value = loWorksheet.Range("E" & CStr(vrow)).Value & firstAddress
        End If
    Next
NextvRow:
  Next vrow
 

abenitez77

Registered User.
Local time
Today, 14:29
Joined
Apr 29, 2010
Messages
141
Code:
Dim rng As Range
For vrow = 12 To 15
    fDesc = loWorksheet.Range("A" & vrow).Value2 & ""
    If fDesc = "" Then GoTo NextvRow
  
    For Each rng In loWorksheet.Range("B27:B40")
        'Set c = .Find(fDesc)
        If InStr(1, rng.Value2 & "", fDesc, vbTextCompare) <> 0 Then
        'If Not c Is Nothing Then
            'Debug.Print rng.Value2
            loWorksheet.Range("E" & CStr(vrow)).Value = loWorksheet.Range("E" & CStr(vrow)).Value & firstAddress
        End If
    Next
NextvRow:
  Next vrow
This is close ...but I need the address of where the
Code:
Dim rng As Range
For vrow = 12 To 15
    fDesc = loWorksheet.Range("A" & vrow).Value2 & ""
    If fDesc = "" Then GoTo NextvRow
   
    For Each rng In loWorksheet.Range("B27:B40")
        'Set c = .Find(fDesc)
        If InStr(1, rng.Value2 & "", fDesc, vbTextCompare) <> 0 Then
        'If Not c Is Nothing Then
            'Debug.Print rng.Value2
            loWorksheet.Range("E" & CStr(vrow)).Value = loWorksheet.Range("E" & CStr(vrow)).Value & firstAddress
        End If
    Next
NextvRow:
  Next vrow
Thanks! close and got me where I needed to be. this is what works for me below. I am putting the values in cells E12 thru E15. I now need to copy those values to columns F thru P. But I need to change the Letters accordingly...ie in column E the values are "=+E29+E36" For column F the values will need "=+F29+F36"..... and G, H , I , etc... all the way to column P. how can I do this easily?

Code:
For vRow = 12 To 15
        xaddr = ""
        fDesc = loWorkSheet.Range("A" & vRow).Value2 & ""
        If fDesc = "" Then GoTo NextvRow
        
        For Each rng In loWorkSheet.Range("B27:B40")
            If InStr(1, rng.Value2 & "", fDesc, vbTextCompare) <> 0 Then
                xaddr = xaddr & "+" & rng.address(False, False)
            End If
        Next
        loWorkSheet.Range("E" & CStr(vRow)).Value = "=" & Replace(xaddr, "B", "E")
NextvRow:
    Next vRow
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:29
Joined
May 7, 2009
Messages
19,169
use Formula instead of value:

...
...
loWorksheet.Range("E" & vrow).Formula = "=" & Replace(xaddr, "B", "E")
 

Users who are viewing this thread

Top Bottom