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

Some unknown number of rows in the beginning

25

26

...

40

41

42

43

44

45

1

2

3

4

.

.

24

25

<tbody>
</tbody>

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

Some unknown number of rows in the beginning

25

26

...

40

41

42

43

44

45

1

2

3

4

.

.

24

25

<TBODY>
</TBODY>

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

Some unknown number of rows in the beginning

25

26

...

40

41

42

43

44

45

1

2

3

4

.

.

24

25

<TBODY>
</TBODY>

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

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

2​

25​

26​

27​

28​

29​

30​

31​

32​

33​

34​

35​

36​

37​

38​

39​

40​

41​

1​

2​

<TBODY>
</TBODY>

Returned 18, i.e., column R

M.