Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in a column
Thanks Norman for the TextBox deletion answer - it was just what I wanted
This problem just shows up how little I know - I do not know how to make your suggestion work. It's late and I'm old and tired and have tried unsuccessfully to set out below a Sub which would crudley do what I am trying to do - it does not work but it might show what I want - please! Thanks Francis Sub FindRowNum() Sheets("Specs").Select 'find extent of col 1 LastUsedRow = Cells(Rows.Count, 1).End(xlUp).Row 'start in row 2 Cells(2, 1).Select 'number to be found NumToFind = InputBox("Find which number?") 'loop to stop at LastUsedRow While ActiveCell.Row <= LastUsedRow 'loop to step down column While ActiveCell.Value < NumToFind ActiveCell.Offset(1, 0).Select Wend RowNum = ActiveCell.Row Wend End Sub "Norman Jones" wrote in message ... Hi Francis, Try something like: '============ Dim RngFound As Range Const sSearchString As String = 44 '<<==== CHANGE Set RngFound = rng.Find(What:=sSearchString, _ After:=rng.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then 'do something, e.g: MsgBox RngFound.Row End If '<<============ --- Regards, Norman "Francis Hookham" wrote in message ... In a subroutine I need to find which row where a specific numeral occurs in a column A. numbers will start in A4 and there may be blank cells in the column. The Module already has the following LastUsedRow = Cells(Rows.Count, 1).End(xlUp).Row to find the range: Range(cells(4,1),cells(LastUsedRow,1)) How do I carry on from there to find NumberRow? Thanks Francis Hookham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in a column
Hi Francis,
'--------------- This problem just shows up how little I know - I do not know how to make your suggestion work. It's late and I'm old and tired and have tried unsuccessfully to set out below a Sub which would crudley do what I am trying to do - it does not work but it might show what I want - please '--------------- Try the following version: '============= Public Sub Tester001() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim RngFound As Range Dim iLastRow As Long Dim Res As String Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Specs") iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = SH.Range("A2:A" & iLastRow) Res = InputBox("Find which number?") If Res = vbNullString Then 'Nothing to find, Exit Sub End If Set RngFound = Rng.Find(What:=Res, _ After:=Rng.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then 'do something, e.g: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With Else MsgBox Prompt:="The string " _ & Res & " was not found", _ Buttons:=vbCritical, _ Title:="Not Found!" End If End Sub '<<============= --- Regards, Norman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in a column
Thanks Norman - just what I wanted.
Francis "Norman Jones" wrote in message ... Hi Francis, '--------------- This problem just shows up how little I know - I do not know how to make your suggestion work. It's late and I'm old and tired and have tried unsuccessfully to set out below a Sub which would crudley do what I am trying to do - it does not work but it might show what I want - please '--------------- Try the following version: '============= Public Sub Tester001() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim RngFound As Range Dim iLastRow As Long Dim Res As String Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Specs") iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = SH.Range("A2:A" & iLastRow) Res = InputBox("Find which number?") If Res = vbNullString Then 'Nothing to find, Exit Sub End If Set RngFound = Rng.Find(What:=Res, _ After:=Rng.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then 'do something, e.g: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With Else MsgBox Prompt:="The string " _ & Res & " was not found", _ Buttons:=vbCritical, _ Title:="Not Found!" End If End Sub '<<============= --- Regards, Norman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in a column
Hi Norman
I have got back to this and have adapted it to find the specified number and it ends up selecting (and colouring) the cell with: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With I don't want to select the cell - I need the row number as a pointer to the next part of the subroutine but I cannot find how to get the row number out of this. RowReqd = RtnFound.Row No, that's no good! Please... Francis Hookham "Norman Jones" wrote in message ... Hi Francis, '--------------- This problem just shows up how little I know - I do not know how to make your suggestion work. It's late and I'm old and tired and have tried unsuccessfully to set out below a Sub which would crudley do what I am trying to do - it does not work but it might show what I want - please '--------------- Try the following version: '============= Public Sub Tester001() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim RngFound As Range Dim iLastRow As Long Dim Res As String Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Specs") iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = SH.Range("A2:A" & iLastRow) Res = InputBox("Find which number?") If Res = vbNullString Then 'Nothing to find, Exit Sub End If Set RngFound = Rng.Find(What:=Res, _ After:=Rng.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then 'do something, e.g: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With Else MsgBox Prompt:="The string " _ & Res & " was not found", _ Buttons:=vbCritical, _ Title:="Not Found!" End If End Sub '<<============= --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in a column
Watch your spelling:
RowReqd = RtnFound.Row may be: RowReqd = RngFound.Row (not sure if RowReqd is spelled correctly, though.) Francis Hookham wrote: Hi Norman I have got back to this and have adapted it to find the specified number and it ends up selecting (and colouring) the cell with: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With I don't want to select the cell - I need the row number as a pointer to the next part of the subroutine but I cannot find how to get the row number out of this. RowReqd = RtnFound.Row No, that's no good! Please... Francis Hookham "Norman Jones" wrote in message ... Hi Francis, '--------------- This problem just shows up how little I know - I do not know how to make your suggestion work. It's late and I'm old and tired and have tried unsuccessfully to set out below a Sub which would crudley do what I am trying to do - it does not work but it might show what I want - please '--------------- Try the following version: '============= Public Sub Tester001() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim RngFound As Range Dim iLastRow As Long Dim Res As String Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Specs") iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = SH.Range("A2:A" & iLastRow) Res = InputBox("Find which number?") If Res = vbNullString Then 'Nothing to find, Exit Sub End If Set RngFound = Rng.Find(What:=Res, _ After:=Rng.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then 'do something, e.g: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With Else MsgBox Prompt:="The string " _ & Res & " was not found", _ Buttons:=vbCritical, _ Title:="Not Found!" End If End Sub '<<============= --- Regards, Norman -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in a column
Dave - I now see why it did not work - I had mistakenly defined
Dim RowReqd As Range instead of Dim RowReqd As Integer All is well now - many thanks Francis Hookham "Dave Peterson" wrote in message ... Watch your spelling: RowReqd = RtnFound.Row may be: RowReqd = RngFound.Row (not sure if RowReqd is spelled correctly, though.) Francis Hookham wrote: Hi Norman I have got back to this and have adapted it to find the specified number and it ends up selecting (and colouring) the cell with: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With I don't want to select the cell - I need the row number as a pointer to the next part of the subroutine but I cannot find how to get the row number out of this. RowReqd = RtnFound.Row No, that's no good! Please... Francis Hookham "Norman Jones" wrote in message ... Hi Francis, '--------------- This problem just shows up how little I know - I do not know how to make your suggestion work. It's late and I'm old and tired and have tried unsuccessfully to set out below a Sub which would crudley do what I am trying to do - it does not work but it might show what I want - please '--------------- Try the following version: '============= Public Sub Tester001() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim rCell As Range Dim RngFound As Range Dim iLastRow As Long Dim Res As String Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Specs") iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = SH.Range("A2:A" & iLastRow) Res = InputBox("Find which number?") If Res = vbNullString Then 'Nothing to find, Exit Sub End If Set RngFound = Rng.Find(What:=Res, _ After:=Rng.Cells(1), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not RngFound Is Nothing Then 'do something, e.g: With RngFound MsgBox .Address(0, 0) .Interior.ColorIndex = 6 .Select End With Else MsgBox Prompt:="The string " _ & Res & " was not found", _ Buttons:=vbCritical, _ Title:="Not Found!" End If End Sub '<<============= --- Regards, Norman -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FInd common data in one column then add number in adjacent column | Excel Worksheet Functions | |||
find last row value in column when using MATCH to find column | Excel Worksheet Functions | |||
Find something in column a then find if column B matches criteria | Excel Discussion (Misc queries) | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Find specific column titles and copy the column to new workboo | Excel Programming |