input/msg box

ryll

New Member
Joined
Nov 7, 2005
Messages
1
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..
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top