Need Help Pls..... Urgent!!

[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.
eg.
Apple
Orange
Pear
Banana
Mango

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..

Thanks!

 

[Rowan Drummond]

Assuming your fruits are on sheet1 and you want to copy the rows to
sheet2 then try:

Sub fin()
Dim fnd As String
Dim fndRng As Range
Dim eRow As Long
fnd = InputBox("Enter a Fruit")
If fnd <> "" Then
With Sheets("Sheet1").Cells
Set fndRng = .Find(fnd)
End With
If Not fndRng Is Nothing Then
eRow = Sheets("Sheet2").Cells(Rows.Count, 1). _
End(xlUp).Row + 1
fndRng.EntireRow.Copy Sheets("Sheet2").Cells(eRow, 1)
End If
End If
End Sub

On the second part of your question I can't see why a =sum() function
would not update if data is changed unless you are wanting to change the
range that is summed in which case you need to post more detail eg what
range do you want to sum, how will this change, what will prompt the
change etc.

Hope this helps
Rowan

 

[ryll]

hey thanks! your solution definitely helps. However as i've tried, it
could only select a row of eg. Orange. If I had plenty rows under the
fruit named Orange, how can i possibly do so?

 

[Rowan Drummond]

Hi Ryll

There are a few ways to do this. The method here assumes you have
headings in row 1 and that the Fruits (or whatever you are looking for)
are in column A.

Sub fin()
Dim fnd As String
Dim fndRng As Range
Dim eRow As Long
Dim lRow As Long
lRow = Sheets("Sheet1").Cells(Rows.Count, 1). _
End(xlUp).Row
fnd = InputBox("Enter a Fruit")
If fnd <> "" Then
With Sheets("Sheet1").Cells(1)
.AutoFilter Field:=1, Criteria1:=fnd
Set fndRng = Rows("2:" & lRow). _
SpecialCells(xlCellTypeVisible)
.AutoFilter
End With
If Not fndRng Is Nothing Then
eRow = Sheets("Sheet2").Cells(Rows.Count, 1). _
End(xlUp).Row + 1
fndRng.Copy Sheets("Sheet2").Cells(eRow, 1)
End If
End If
End Sub

Regards
Rowan