Select cell based on row number and string found in range. help?

lpking2005

Board Regular
Joined
Mar 21, 2011
Messages
140
Hi everyone,

Im currently having trouble trying to select a cell based on a found rownumber and a string found in a range.

My code basically searches the range("I5:M5") to find the day name and then searches the "Lines" column to find the line number eg. 604.
Then i need it to select the cell that intersects.

Im currently playing around with this bit of code.
Code:
    Set FindDay = ThisWorkbook.ActiveSheet.Range("I5:M5").Find(What:=curDayName, LookIn:=xlValues)
    Set FindLine = ThisWorkbook.ActiveSheet.Range("B:B").Find(What:=cLine, LookIn:=xlValues)
    FindDay.Select
    ThisWorkbook.ActiveSheet.Range(FindLine, FindDay).Select

You should be able to get an idea from this picture.

Any help is much appreciated!
Thanks.

 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,239
How do you decide which day you're looking for?
 

lpking2005

Board Regular
Joined
Mar 21, 2011
Messages
140
Here is my whole code...

Code:
Sub getData()

Dim curDate As Date
Dim curDayNum As String
Dim curDay As String
Dim curDayName As String
Dim curMonth As String
Dim curWeek As String
Dim curYear As String
Dim dCode As String
Dim cLine As Integer
Dim fPath As String
Dim fName As String
Dim match As Range
Dim findMe As String
Dim TotalQTY As String
Dim FindLine As Range
Dim FindDay As Range
    
'Set daycode
curDayNum = DateDiff("d", DateSerial(Year(curDate), 1, 0), curDate)
'
If Len(curDayNum) <= 3 Then
    curDayNum = "0" & curDayNum
End If
'




curDate = Now
curYear = Format(Date, "yyyy")
curMonth = Format(Date, "mm")
curDay = Format(Date, "dd")
curDayName = Format(Date, "ddd")
If curDayName = "Thu" Then curDayName = "Thur"
dCode = curYear & curMonth




For cLine = 601 To 604


      
Select Case cLine


'These numbers will be skipped
Case 601, 603
'    goto next line




Case Else


    'set the path and file names
    'fPath = n & ":\Production\PROD POWDERS\" & Lnum & " Time Sheets"
    fPath = "C:\Users\lpkin_000\Dropbox\Firming Meeting Macro\"
    'fArchive = "\"
    fName = cLine & " Time Sheet " & dCode & ".xlsm"
    
    'open timesheets
    Workbooks.Open Filename:=fPath & fName, ReadOnly:=True


    'Goto correct day sheet
    ActiveWorkbook.Sheets("Day " & curDay).Select
    
    'Get Total units produced
    Set match = ActiveSheet.Cells.Find("Total")
    TotalQTY = match.Offset(, 2).Value
    
    'close workbook
    ActiveWorkbook.Close


    'Input in correct cell
    'make sure correct sheet is selected!!
    Set FindDay = ThisWorkbook.ActiveSheet.Range("I5:M5").Find(What:=curDayName, LookIn:=xlValues)
    Set FindLine = ThisWorkbook.ActiveSheet.Range("B:B").Find(What:=cLine, LookIn:=xlValues)
    FindDay.Select
    ThisWorkbook.ActiveSheet.Range(FindLine, FindDay).Select
    ThisWorkbook.ActiveSheet.Range(ActiveCell, ActiveCell.Offset(FindLine.Row, 0)).Select
    'FindLine.Offset(
    
    
End Select
Next


End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,239
If 'FindDay' is returning the day of the week correctly and if 'FindLine' is returning the number correctly, then the following code should select the intersection of the two cells:
Code:
Cells(FindLine.Row, FindDay.Column).Select
 

lpking2005

Board Regular
Joined
Mar 21, 2011
Messages
140
Thanks very much! I already had it but just missing the .row and .column included. :rolleyes:
 

Watch MrExcel Video

Forum statistics

Threads
1,128,107
Messages
5,628,729
Members
416,333
Latest member
Time2Learn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top