input/msg box

[ryll]

Hi. I am quite new at VBA programming and would like some help.


I have a raw datasheet and i wished to do a program to get an output into another worksheet of the same workbook using an input/msg box to allow users type in the 5 different fields i have in my first column in my raw datasheet.
eg.
Apple
Orange
Pear
Banana
All

I hope somebody could help me with this.

Previously i have this

Sub GetRange()
Dim Rng As Range
On Error Resume Next
Set Rng = Application.InputBox(prompt:="Enter range", Type:=8)
If Rng Is Nothing Then
MsgBox "Operation Cancelled"
Else
Rng.Select
End If

which help me to getRange from my worksheet. However, I wish to have an imput box that allows the user to enter, e.g ENTER A FRUIT. And If i type Banana. The whole row which contains Banana should be paste into another worksheet. Is it possible?

Another question i have is, i hope to sum up some counts for different specifications. I noe using =sum() function could help. However, i would like to do a simple VBA program so that it will automatically update the sum for me in future when i have an update in various specifications. How can i do it? I hope somebody could help me with this too..

 

[venkat1926]

for your first question try something like this

Public Sub test()
On Error GoTo line1
Dim x As String
x = InputBox("type name of the fruit")
Worksheets("sheet1").Activate
Range("a1").Select
Cells.Find(x).Activate
Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Worksheets("sheet2").Activate
Range("a2").PasteSpecial
line1:
MsgBox "there is no such furit in your list"

End Sub


modify to suit you
regarading the second question you can have an event macro in that particular sheet so that whenever you make any change in any cells in thes sheet the summ will be calculated and given as msg box. Moredetails may be required.