Using find command

[http://www.exce]

Dear all:
I'm trying to use "find" command to search a particular word (460V, 230V etc..) in a SPECIFIC cell. The cell is under row "R". It will identify the string when the argument is satisfied. Here is what I did:

With Worksheets("Import").Range("R1:R500")
Set Dummy2 = .Find("230v", LookIn:=xlValues)
If Not Dummy2 Is Nothing Then
VOLT = "230V"
Else
Set Dummy2 = .Find("208v", LookIn:=xlValues)
If Not Dummy2 Is Nothing Then
VOLT = "208V"
Else
Set Dummy2 = .Find("460v", LookIn:=xlValues)
If Not Dummy2 Is Nothing Then
VOLT = "460V"
Else
Set Dummy2 = .Find("380v", LookIn:=xlValues)
If Not Dummy2 Is Nothing Then
VOLT = "380V"
Else
VOLT = "TBD VOLTAGE"

The problem is the command shows above will search all cells in row "R". Some time there will be conflict because there will be a situation when one cell showing "480V" while other cell showing "380V". The cell that has "380V" is actually the cell that I will like my program to seach on. How can I modify my command so it will search on a specific cell. You help is much appreciated.

Best Regards
Howard

[antoka05]

The problem is clear but it isn't the same for what you want to obtain.

The macro you published resolve the problem.
if you have some cells with '380V' and one cell (or more) with '480V' when you search for '480V' you will obtain the macro will find it.

In case of more than one value on the column 'R' what do you need? You want to display the distinct of all values present in the column?

Thanks,
Antonio

[http://www.exce]

Antonio:
Thank you for your reply. To be more specific of what I want to obtain, Each of 2 cells will show a sentence. For example:

"Sheet 1"
A1 cell shows, “General statement"
R1 cell shows, “the motor is capable of 208V, 230V, 460V"
A2 cell shows, “What we have"
R2 cells shows, “we are having 208V generator"

I will like the program reading R2 cells and print out "208V" on sheet 2.
Please aware that sometime R2, A2 sentence can be R1, A1 and R1, A1 sentence can be R2, A2.

Many Thanks

[http://www.exce]

Hello all, Is there anyone that can help me out? Macro is very new to me and I'm really scratching my head on this. I think what needs to be done is to write a line that will recognize the sentence "What we have” (Either on cell A2or A1). Once the sentence is recognized, it will only search the cell that next to the sentence, "we are having 208V generator”, at either R1 or R2.

[foxbeat]

This code works, you may edit it to fit your needs.

Sub test()



Dim i As Integer
Dim j As Integer
Dim DataSearch As Variant
Dim SearchFor As Variant
Dim Found(100) As Variant
Dim rng As Range
Dim c As Variant

'In columns A write in separete rows the values you want to search (380V,200v, etc)
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
DataSearch = rng.Value

For i = 1 To UBound(DataSearch, 1)

SearchFor = DataSearch(i, 1)

With ActiveSheet.Range("R1:R500")

Set c = .Find(SearchFor, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
j = j + 1
Found(j) = c.Value
End If
End With

Next i

'Paste results

Cells(1, 2) = "The motor is capable of"
For i = 1 To 100

If IsEmpty(Found(i)) = False Then
Cells(1 + i, 2) = Found(i)
End If

Next i




End Sub