VBA search and retunr results - simple programming

Hi,

I know this is probably basic programming for some of you: -) but after spending all afternoon watching youtube videos and getting
the same error message 'For without next' and 'Cannot execute code without break' I finally give up. ;-(
I have no training or knowledge of excel apart from as a user.

PLEASE HELP!!!
ok....ill keep it simple

on sheet1 I have a search box to enter 'store number; (and a button to link to the macro).
if a match is found on sheet2 column A (sheet 2 has 5 columns) results should be displayed back on sheet1 D6,E6,F6,G6,H6
there is more than one entry for the same store on sheet 2 they all need to be displayed on sheet 1 and its not in order.

this what I've tried so far.....copying a youtube video but it was design to only return one row I need all matches to be returned....
and it didn't work anyway.

     
Sub searchdata()

Dim erow As Long
Dim ws As Worksheet
Dim lastrow As Long
Dim count As Integer

lastrow = Sheets("Data2").cell(Rows.count, 1).End(x1up).Row

For X = 2 To lastrow
If Sheets("Data2").cellss(X, 1) = Sheet1.Range("b4") Then
Sheet1.Range("E6") = Sheets("data2").Cells(X, 1)
Sheet1.Range("F6") = Sheets("data2").Cells(X, 2)
Sheet1.Range("G6") = Sheets("data2").Cells(X, 3)
Sheet1.Range("H6") = Sheets("data2").Cells(X, 4)
Sheet1.Range("I6") = Sheets("data2").Cells(X, 5)
End If
        
End Sub

PS. This spread sheet is for a bank and I can't upload any of the information or onto here :-(

Hi renangv

Your post does not comply with Rule 3 of the Forum....

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Please attach a sample workbook with a before & after scenario making sure that your desired solution is shown - mock up the results manually.

from what I can see, you need to add

Dim X As Long

with the rest of the dim statements. And add

Next X

right after the End If. Note that if there are multiple results found, the last result will overwrite the preceeding ones.

Perhaps this can start you off. Would be better seeing a sample file.

Option Explicit

Sub StoreNo_Find()
Dim lastRow As Long
Dim ws As Worksheet
Dim r As Integer, x As Integer, c As Integer
Dim storeNo As String
storeNo = InputBox("Enter Store Number")
lastRow = Sheets("Sheet2").Cells(Rows.count, "A").End(xlUp).Row
x = 5
For r = 1 To lastRow
    For c = 1 To 5
        If Sheets("Sheet2").Cells(r, c) = storeNo Then
            Sheet1.Cells(6, x) = Sheets("Sheet2").Cells(r, c)
            x = x + 1
        End If
    Next c
Next r
End Sub

You added the pics and not said anything about any of the solutions that have been given. Are the codes not working? If not what is not working?

Here you go...

Option Explicit

Private Sub CommandButton1_Click()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Data2")
Dim nextRow As Long
Dim lastRow As Long
Dim Indicator As String
Dim ws As Worksheet
Dim r As Integer, x As Integer, c As Integer
Dim storeNo As String
storeNo = ws1.Range("B4").Value
nextRow = ws1.Range("D" & Rows.count).End(xlUp).Row + 1
Indicator = False
With ws2
    lastRow = ws2.Cells(Rows.count, "A").End(xlUp).Row
    For r = 2 To lastRow
        If .Cells(r, 1) = storeNo Then
            ws1.Cells(nextRow, 4).Resize(, 5) = Array(ws2.Cells(r, 1), ws2.Cells(r, 2), ws2.Cells(r, 3), ws2.Cells(r, 4), ws2.Cells(r, 5))
        nextRow = nextRow + 1
        Indicator = True
        End If
    Next r
    If Indicator = False Then
        MsgBox "No entries found for that No", vbInformation, ""
    End If
End With
End Sub

This one should cover that.

Private Sub CommandButton1_Click()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim nextRow As Long, r As Long
Dim storeNo As String, Indicator As Boolean

Set ws1 = Sheets("Sheet1"): Set ws2 = Sheets("Data2")
Indicator = False

With ws1
    .Cells(5, 4).CurrentRegion.Offset(1).ClearContents
    storeNo = .Range("B4").Value
    nextRow = .Range("D" & Rows.count).End(xlUp).Row + 1
End With

With ws2
    For r = 2 To .Cells(.Rows.count, "A").End(xlUp).Row
        If .Cells(r, 1) = storeNo Then
            ws1.Cells(nextRow, 4).Resize(, 5) = Array(.Cells(r, 1), .Cells(r, 2), .Cells(r, 3), .Cells(r, 4), .Cells(r, 5))
        nextRow = nextRow + 1: Indicator = True
        End If
    Next r
    If Indicator = False Then MsgBox "No entries found for that No", vbInformation, ""
End With
End Sub

Yep, that covers it ....bakerman2 to the rescue lol

@ bakerman2

Indicator As Boolean

Of course...Silly me.

Pleasure...Thanks for rep points

@ renangv

You're welcome and thanks for rep+.