Find a value only in the first column af a named range

[Harry Covair]

Hi the forum,

Do some people can help me to solve the following VBA problem?

In a worksheet I have a set of three named ranges each of three columns (thus nine columns).

I try to find a VBA solution to find a specific value in the FIRST column of one of the three named range.

Example

Range1 = columns A, B, C

Range2 = columns D, E, F

Range3 = columns G, H, I

The value I want to find is e.g. in Range2, column D but also in column F. Only the value found in column D interests me.

Any help is welcome

Thanks in advance


Harry

 

[Marc L]

Hi ! You could operate a Find (see VBA's Help) upon a Range.Columns(1).Cells for example ...


Have you write any code ?

 

[Harry Covair]

Hi Marc L,

Thanks for your answer

The "Find help" does not help


See here my actual Find code (I don't know how to use blockquote code)

If Trim(funct) <> "" Then

With Sheets("Base").Range(range_source)

Set rslt_find = .Find(What:=funct, _

After:=.Cells(.Cells.Count), _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlByRows, _

SearchDirection:=xlNext, _

MatchCase:=False)

If rslt_find Is Nothing Then

MsgBox "Mistake! Function " & funct & " not found"

cnt_mist = cnt_mist + 1

GoTo next_function

End If

End With

End If

As told in my first post, the range "range_source" refers to one of the three ranges and contains three columns and what I want is to find the content of the variable "funct" only in the first of them.


thank you in advance for your advice

Regards

Harry

 

[Luke M]

Perhaps this will help?

Set SearchRange = range_source.resize(range_source.Rows.Count,1)

Defines a range that is only the first column of whatever [code]range_source is.

[pre]Dim SearchRange As Range
If Trim(funct) <> "" Then
Set SearchRange = range_source.Resize(range_source.Rows.Count, 1)
With SearchRange
Set rslt_find = .Find(What:=funct, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rslt_find Is Nothing Then
MsgBox "Mistake! Function " & funct & " not found"
cnt_mist = cnt_mist + 1
GoTo next_function
End If
End With
End If

EDIT: Looking at your first code, it looks like range_source is actually a string variable, not a range. If this is true, corrected code:

Dim SearchRange As Range
If Trim(funct) <> "" Then
With Worksheets("Base&#34.Range(range_source)
Set SearchRange = .Resize(.Rows.Count, 1)
End With
With Sheets("Base&#34.Range(range_source)
Set rslt_find = SearchRange.Find(What:=funct, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rslt_find Is Nothing Then
MsgBox "Mistake! Function " & funct & " not found"
cnt_mist = cnt_mist + 1
GoTo next_function
End If
End With
End If[/code][/pre]

 

[Marc L]

Harry, should be OK with this mod in your code :  

With Sheets("Base").Range(range_source).Columns(1)

And maybe you should delete the After argument ...

 

[Harry Covair]

Hi Luke M,

Thanks for your answer.

Indeed the variable "range_source" is a string. Here the reason:

Case "English"

range_source = "Range_EN"

Range_EN contains three columns with function names in three languages; the first column is in english.

E.g. employee employé mitarbeiter


I replaced my actual code with the one of your last edit (copy-paste).

Running the application, none of the content of the variable "funct" is found (rslt_find Is Nothing = true)

Any idee?

Thanks in advance

Harry

 

[Harry Covair]

Hi Marc L,


Your latest suggested modification solves the problem.


Many thanks for your help

Harry

 

[Marc L]

Thanks for your feedback Harry !


Glad to help out,   Regards.