VBA to Scroll Down to specific text

[emacjake]

Hi All

I'm looking for some help with a code to select a range based on its headed and footer in Column A
I've got the below code written to find the text 'Bistro' and select the entire top row of the data,
I'm looking to then scroll down to the next instance of the text 'Bistro' in column A so i can then selection.copy

Can it be written into Something like this "Range(Selection, Selection.End(xlDown)).Select" ?




Cells.Find(What:="bistro", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(0, 5).Activate
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select




Thanks!!!!!!!

 

[Mohammad Basem]

Hi emacjake,

Please post sample data and show exactly what you want to achieve.

 

[Fishboy]

Hi emacJake,

Try the following code:

Sub SelectFindValue()
' Define variables
Dim FindString As String, Rng As Range
' Sets FindString as whatever value you put in the input box
    FindString = InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        With Sheets("Sheet1").Cells
' Sets Rng as the cell containing the searched value
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
' If the value is found
            If Not Rng Is Nothing Then
' Select the cell
                Rng.Select
            Else
' Otherwise show a message that the value could not be found
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

 

[emacjake]

I figured it out,

[TABLE="width: 744"]
<tbody>[TR]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]Gross Sales[/TD]
[TD]Discounts[/TD]
[TD]Commission[/TD]
[TD]Returns[/TD]
[TD]Net Qty[/TD]
[TD]Net Sales[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Bistro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Non-Alcoholic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Non-Alcoholic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Coke 600ml[/TD]
[TD="colspan: 2"]$124[/TD]
[TD]-$3[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]34[/TD]
[TD]$121[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Coke Zero 600ml[/TD]
[TD="colspan: 2"]$29[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]8[/TD]
[TD]$29[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Bistro[/TD]
[TD="colspan: 2"]$100[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]$100[/TD]
[TD]10[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Outlet 2[/TD]
[TD="colspan: 2"]$29[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]8[/TD]
[TD]$29[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Fanta 600ml[/TD]
[TD="colspan: 2"]$40[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]11[/TD]
[TD]$40[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 744"]
<tbody>[TR]
[TD="class: xl66, width: 216, colspan: 2"]Outlet 2[/TD]
[TD="class: xl65, width: 96, colspan: 2"]$100[/TD]
[TD="class: xl65, width: 84"]$100[/TD]
[TD="class: xl65, width: 96"]$100[/TD]
[TD="class: xl65, width: 84"]$100[/TD]
[TD="class: xl65, width: 72"]10[/TD]
[TD="class: xl65, width: 96"]$100[/TD]
[/TR]
</tbody>[/TABLE]


Data is in the format as above,
I needed to find all columns and rows of outlet data and then insert a a blank row below so i could, Range(Selection, Selection.End(xlDown)).Select, and copy all sales figure to a different page and then do again for every operating outlet.






Dim Outlet As Range


'Bistro
Sheets("Current Stocktake Sales Figures").Select
Range("A1").Select

'Find all bistro Sales figures and insert row underneath

Set Outlet = Cells.Find(What:="bistro", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

If (Not Outlet Is Nothing) Then

Cells.Find(What:="bistro", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(0, 5).Activate


Cells.FindNext(After:=ActiveCell).Offset(1, 7).Activate
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.insert Shift:=xlDown

'Select all bistro Figures
Cells.Find(What:="bistro", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Offset(0, 5).Activate


Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'Paste to Bisty Sheet
Sheets("Bisty").Activate
Range("N4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

GoTo Outlet 2
Else
End If

 

[Fishboy]

Glad to hear you got it sorted