error trying to get cells column value

[a.hudrea]

Sub abc()
Dim stfc, ptfc, pmfc As Double
Dim i, j As Integer
Dim mvAs String
Dim lngMonthNo, lngMonthNo2, rngsearch, rngsearch2 As Range
Dim st2, st3 As Worksheet
Set st2 = Sheets("x")
Set st3 = Sheets("y")

mv= st3.Range("b8").Value


For j = 0 To 3 ' nr de categorii
    For i = 0 To 9 'nr de furnizori

Set rngsearch = Range("f8:f19")
lngMonthNo = rngsearch.Find(what:=mv, LookIn:=xlValues, lookat:=xlWhole).Row

st2.Select
Set rngsearch2 = st2.Range(st2.Cells(4, 6), st2.Cells(4, 83))
lngMonthNo2 = rngsearch2.Find(what:=mv, LookIn:=xlValues, lookat:=xlWhole).Column

"Object variable or With block variable not set (Error 91)" is the error i get on the last line of code.

[NeedForExcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[JOHN H. DAVIS]

Maybe:

Sub a_hudrea()
Set lngMonthNo2 = rngsearch2.Find(what:=mv, LookIn:=xlValues, lookat:=xlWhole).Column
End Sub

[a.hudrea]

test1.xlsm
What I need is a value for the row where the value from the dropdown list is found on the Y sheet, and a value for the column in the X sheet.

Doesnt work John, sry.

[JOHN H. DAVIS]

Maybe:

Sub a_hudrea()
Dim stfc, ptfc, pmfc As Double
Dim i, j As Integer
Dim valoareluna As String
Dim lngMonthNo, lngMonthNo2, rngsearch, rngsearch2 As Range
Dim st2, st3 As Worksheet
Set st2 = Sheets("x")
Set st3 = Sheets("y")

mv = st3.Range("b4").Value




Set rngsearch = Range("d3:d5")
    lngMonthNo = rngsearch.Find(what:=mv, LookIn:=xlValues, lookat:=xlPart).row
MsgBox lngMonthNo


st2.Select

Set rngsearch2 = st2.Range("c2:r2")
    'rngsearch2.Select    
lngMonthNo2 = rngsearch2.Resize(2).Find(what:=mv, LookIn:=xlValues, lookat:=xlPart).Column

MsgBox lngMonthNo2
End Sub

[NeedForExcel]

That is happening because you have merged cells.

If you change to this line, it will work

Set rngsearch2 = st2.Range("c19:C21")

[a.hudrea]

I'm trying something, will comeback with a reply.

[JOHN H. DAVIS]

Umm. Worked for me on the sample you provided. Msgbox with 4 then a msgbox with 8. Note changes too code to be made.

Sub a_hudrea()
Dim stfc, ptfc, pmfc As Double
Dim i, j As Integer
Dim valoareluna As String
Dim lngMonthNo, lngMonthNo2, rngsearch, rngsearch2 As Range
Dim st2, st3 As Worksheet
Set st2 = Sheets("x")
Set st3 = Sheets("y")

mv = st3.Range("b4").Value




Set rngsearch = Range("d3:d5")
    lngMonthNo = rngsearch.Find(what:=mv, LookIn:=xlValues, lookat:=xlPart).row
MsgBox lngMonthNo


st2.Select

Set rngsearch2 = st2.Range("c2:r2")
    'rngsearch2.Select    
lngMonthNo2 = rngsearch2.Resize(2).Find(what:=mv, LookIn:=xlValues, lookat:=xlPart).Column

MsgBox lngMonthNo2
End Sub

[a.hudrea]

Sorry for the late reply. It does work John ty so much guys for the help. I did not notice the resize part.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.