Search for exact match of string in excel row using VBA Macro

[vamshedhar]

I am having a row with values from 21 to 45 and continued again from 1 to 25

[TABLE="width: 500"]
<tbody>[TR]
[TD]Some unknown number of rows in the beginning[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]...[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD].[/TD]
[TD].[/TD]
[TD]24[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

to find column position of "1" i am using code

Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

But it is returning column number of 31 and when i delete or hide that column it returns column number of 41.

Cant i search for exact value 1 in my row???

 

[Marcelo Branco]

I am having a row with values from 21 to 45 and continued again from 1 to 25

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Some unknown number of rows in the beginning
[/TD]
[TD]25
[/TD]
[TD]26
[/TD]
[TD]...
[/TD]
[TD]40
[/TD]
[TD]41
[/TD]
[TD]42
[/TD]
[TD]43
[/TD]
[TD]44
[/TD]
[TD]45
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD]24
[/TD]
[TD]25
[/TD]
[/TR]
</TBODY>[/TABLE]

to find column position of "1" i am using code

Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

But it is returning column number of 31 and when i delete or hide that column it returns column number of 41.

Cant i search for exact value 1 in my row???

Try

Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

M.

 

[JLGWhiz]

I am having a row with values from 21 to 45 and continued again from 1 to 25

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Some unknown number of rows in the beginning
[/TD]
[TD]25
[/TD]
[TD]26
[/TD]
[TD]...
[/TD]
[TD]40
[/TD]
[TD]41
[/TD]
[TD]42
[/TD]
[TD]43
[/TD]
[TD]44
[/TD]
[TD]45
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD]24
[/TD]
[TD]25
[/TD]
[/TR]
</TBODY>[/TABLE]

to find column position of "1" i am using code

Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

But it is returning column number of 31 and when i delete or hide that column it returns column number of 41.

Cant i search for exact value 1 in my row???

change this: LookAt:=xlPart,

To this: LookAt:=xlWhole,

 

[vamshedhar]

Try

Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

M.

Runtime error '91':

Object variable or With block variable not set

 

[Marcelo Branco]

Runtime error '91':

Object variable or With block variable not set

Try

Sub aTest()
    Dim myCol As Long
            
    myCol = Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
    MsgBox myCol
        
End Sub

M.

 

[vamshedhar]

Try

Sub aTest()
    Dim myCol As Long
            
    myCol = Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
    MsgBox myCol
        
End Sub

M.

No improvement Sir... Same error repeats again.

 

[vamshedhar]

change this: LookAt:=xlPart,

To this: LookAt:=xlWhole,

Runtime error '91':

Object variable or With block variable not set

 

[Marcelo Branco]

No improvement Sir... Same error repeats again.

Try this

Sub aTest()
    Dim myCol As Long, rngFound As Range
    
    Set rngFound = Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not rngFound Is Nothing Then
        myCol = rngFound.Column
        MsgBox myCol
    Else
        MsgBox "Not Found"
    End If
    
End Sub

What message do you get?

M.

 

[vamshedhar]

Try this

Sub aTest()
    Dim myCol As Long, rngFound As Range
    
    Set rngFound = Worksheets("sheet1").Rows(2).Find(What:="1", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not rngFound Is Nothing Then
        myCol = rngFound.Column
        MsgBox myCol
    Else
        MsgBox "Not Found"
    End If
    
End Sub

What message do you get?

M.

Sir,
It says Not Found But there exists a 1 in the row...
When i tried to search for blank field keeping What:="" it gave correct position
and sir the values 25, 26, 27 are week numbers generated by N2=WEEKNUM(N3,1) where N3=1/Sep

 

[Marcelo Branco]

Sir,
It says Not Found But there exists a 1 in the row...
When i tried to search for blank field keeping What:="" it gave correct position
and sir the values 25, 26, 27 are week numbers generated by =WEEKNUM(N3,1) where N3=1/Sep

Sorry, I'm not following you. If there is a 1 in row 2 of Sheet1 it should work.

Worked for me


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[TD]

Q​

[/TD]
[TD]

R​

[/TD]
[TD]

S​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

25​

[/TD]
[TD]

26​

[/TD]
[TD]

27​

[/TD]
[TD]

28​

[/TD]
[TD]

29​

[/TD]
[TD]

30​

[/TD]
[TD]

31​

[/TD]
[TD]

32​

[/TD]
[TD]

33​

[/TD]
[TD]

34​

[/TD]
[TD]

35​

[/TD]
[TD]

36​

[/TD]
[TD]

37​

[/TD]
[TD]

38​

[/TD]
[TD]

39​

[/TD]
[TD]

40​

[/TD]
[TD]

41​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[/TR]
</TBODY>[/TABLE]


Returned 18, i.e., column R

M.