Find and past button

[_Fly_]

a

b

c

d

e

f

g

h

i

1

Number

2

Item

3

Stock

4

Condition

5

<tbody>
</tbody>

Sheet Entrance

	a	b	c	d
1	Number	Item	Stock	Condition
2	123456	Paper Case	100	New
3	234123	Chair	4	Used

<tbody>
</tbody>

Sheet Global

Hi my friends it's possible to help me on that issue?
I want to put a code on my button that appears on the Sheet Entrance that when i click appears a msgbox aske me for a number. Then i put the number and click ok, then the code searches on the sheet global for that code and if that number exists copy the interior of the correspondent cell on the column a, b, c and d to the Sheet entrance on the correspondent cells C1, C2, C3, C3. If the number doens't exists appear that info.

Example i click the button on the sheet Entrance and a msgbox appear ask me a number i insert the 234123 and click ok.
The code goes search for that number on A:A and finds on the A3 cell, so it copys the a3 value from Sheet Global to C1 Sheet Entrance, the b3 value from Sheet Global to the c2 Sheet Entrance, the c3 value from Sheet Global to the d3 Sheet Entrance, the d3 value from Sheet Global to the c4Sheet Entrance.

It's possible to give me any ideas to the code?
Regards

 

[onlyadrafter]

Hello,

does this code work as expected?

Sub GET_DATA()
    MY_NUMBER = InputBox("Please enter Number")
    With Sheets("Global")
        For MY_ROWS = 2 To Sheets("Global").UsedRange.Rows.Count
        a = .Range("A" & MY_ROWS).Value
            If .Range("A" & MY_ROWS).Value = Int(MY_NUMBER) Then
                .Range("A" & MY_ROWS & ":D" & MY_ROWS).Copy
                Range("C1").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
                    , Transpose:=True
                End
            End If
        Next MY_ROWS
    End With
End Sub

This assumes that the data for the number is actually a number. If any of these has text in, will need to re-code.

 

[_Fly_]

Hello my friend on the msgbox i will insert a number but as you can see on my Global Sheet the value to copy have numbers or text. It's also possible to insert a msgbox that appears if the number i want to search doesn't exists on the Global Sheet?

Regards

 

[onlyadrafter]

Hello,

is this as expected?

Sub GET_DATA()
    MY_NUMBER = InputBox("Please enter Number")
    With Sheets("Global")
        For MY_ROWS = 2 To Sheets("Global").UsedRange.Rows.Count
            If Format(.Range("A" & MY_ROWS).Value, "#") = MY_NUMBER Then
                .Range("A" & MY_ROWS & ":D" & MY_ROWS).Copy
                Range("C1").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
                    , Transpose:=True
                End
            End If
        Next MY_ROWS
        MY_INPUT = MsgBox("Number not in list", vbOKOnly, "NO MATCH")
    End With
End Sub

 

[L. Howard]

Hi Fly,

Try this in the 'Sheet Entrance' module.

Assign to a button and when run, enter the 'Number' in the Input Box"

Regards,
Howard

Option Explicit

Sub SheetGlobalLookUp()

Dim LRow As Long
Dim aRng As Range, rngFnd As Range
Dim myFnd As String

myFnd = InputBox("Enter the item to search for")

If myFnd = "" Then
    Exit Sub
  ElseIf IsNumeric(myFnd) Then
    myFnd = Val(myFnd) '/ converts a "text" number to a value
  Else
    '/ is text and that is okay
End If

With Sheets("Sheet Global")

    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rngFnd = Sheets("Sheet Global").Range("A2:A" & LRow).Find(What:=myFnd, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)

    If Not rngFnd Is Nothing Then
        rngFnd.Resize(1, 4).Copy
        Sheets("Sheet Entrance").Range("C1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
      Else
        MsgBox "No match found."
    End If

End With
End Sub

 

[_Fly_]

Howard thank's a lot for your kindness but on last code the values appear on a column and i what them on a specific cell. Please what i really want is here can you check that?

http://www.mrexcel.com/forum/excel-...plications-issue-codebar-reader-too-pics.html

 

[L. Howard]

Hi Fly,

Instead of picture, can you post a Link to a sample workbook that has some sample data and tell again what you want to do.

I use Drop Box, but there are others.

There is a small error in the code if posted, but it will not do what you are now asking. So we can leave it alone for now.

Howard

 

[_Fly_]

Howard please see the excel file
https://drive.google.com/file/d/0B1UTQJwIdfeaRHNxTEhCMzN0MHc/view?usp=sharing

 

[_Fly_]

Howard as you can see that appear three buttons on the Saidas sheet.

I want to click on the Procurar button and then that appears a msgbox asking me to insert a codebar number. After that he code goes seach on the Registos Globais sheet column A and after find the row that contain that number copy the value existent on the various cells correspondent to small blue square boxes on the Saidas Sheet.

The Inserir button is to update the Stock. I click that button and the value that is on the Stock final (Sheet Saidas) is copied to the old value existent on the Registos Globais Sheet the Stock Actual.

The Limpar button is already codded by me it's to clean

Did you understand what i want?
Regards

 

[L. Howard]

Hi Fly,

Here a first shot at what I think you want.

Merged cells have been removed, and I added some test data to play with.

Try and see what needs to be changed or added etc.

Howard


https://www.dropbox.com/s/w631zpz8rs8oa39/Stock Materiais v1.xlsm?dl=0


Just now read your last post. Some changes will be needed.

H