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.

 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
How do you decide which day you're looking for?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,657
Messages
6,120,764
Members
448,991
Latest member
Hanakoro

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
Back
Top