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???

 

[vamshedhar]

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.

Sir,

The code is working fine with xlPart when i try to find 41. But When i change it to xlWhole says not found.

As an Extra info:

Those numbers in row two are Week Numbers generated From dates in Row 2.

C2 = WEEKNUM(C3,1)

Where A3 = 30/Jun

Did This create any problem???

 

[Marcelo Branco]

Sir,

The code is working fine with xlPart when i try to find 41. But When i change it to xlWhole says not found.

As an Extra info:

Those numbers in row two are Week Numbers generated From dates in Row 2.

C2 = WEEKNUM(C3,1)

Where A3 = 30/Jun

Did This create any problem???

I'm confused with

C2 = WEEKNUM(C3,1)

Where A3 = 30/Jun

Could you post a data sample and show us in which cell the formula WEEKNUM returns a 1?

M.

 

[vamshedhar]

I'm confused with



Could you post a data sample and show us in which cell the formula WEEKNUM returns a 1?

M.

C2 = WEEKNUM(C3,1)

Where C3 = 30/Jun

Im sorry itz C3 not A3

When date is 1/Jan it WEEKNUM returns 1

 

[Marcelo Branco]

C2 = WEEKNUM(C3,1)

Where C3 = 30/Jun

Im sorry itz C3 not A3

When date is 1/Jan it WEEKNUM returns 1

Sheet1 data


[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]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]
[TR]
[TD]

3​

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

01/jan​

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


Formula in R2
=WEEKNUM(R3,1)

The code in #8 returned 18 for me. I don't why it's not working for you. Maybe someone else can help.

M.