Excel formula question

[qmann]

I have a situation where i want to take any values in a range of G8:J30 and if the values in column D are less than $10 i want to cut the values out of the row G8:J30 and paste them where i have the formula. Any ideas? thanks for the help

 

[KenWright]

OK, you may need to clarify this somewhat. What ranges in Col D? What values do you want cut from G8:J30 and pasted to where in Col D? Bear in mind that you have the benefit of the sheet in front of you, and know in your own mind what you need to achieve - We have to rely on you articulating the question in such a fashion that we are in the same position wrt that as you.

Explaining the need for the Cut bit will help also, as most in here would tend towards a formula that would put the required value in based on your criteria, rather than hardcoding anything via cut and paste.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[qmann]

sorry about the mess up, here is an example

part #1 2 3 $32
part #2 5 3 $3939
part #3 8 9 $0.13
part #4 9 4 $0.00

MISC
value of formula

What i want to do is search that area as a whole. If in the last column the value is less than $10.00 i want to take the value that is above cut it and paste it under 'MISC'. I hope this clears it up a bit.

 

[KenWright]

OK, you can either:-

a) Use Autofilter and filter for the data you want in Situ (I know this doesn't put it under Misc)

b) Use Autofilter and filter for the data you want in Situ, then select the data, do Edit / Go To / Special / Visible cells only, then copy and apste the data under Misc.

c) Use Advanced Filter to filter the data and put what you want under Misc automatically.

Examples of all these methods are available at the following link:-

Autofilter:-

http://www.contextures.com/xlautofilter01.html

Advanced Filter:-

http://www.contextures.com/xladvfilter01.html

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[qmann]

i understand using the filter command but there are a number of these sections i must apply it to and a little help to start a formula or a macro would be great. It's just if i have 200 or so pages this would be very time consuming and i would like to minimize that as much as possible.

Thanks for your reply

Quentin

 

[KenWright]

OK, but once again you need to clarify for us. On each of these pages is the data in the same ranges, ie are the headings the same and in the same places with varying sets or rows of data below them, or are they just all over the place?

If you effectively have a carbon copy of your sheets structure some 200 times, just with differing amounts of rows of data on each sheet, then this is simple enough, but if it is random lists etc then that gets a lot harder.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[qmann]

they will be random lists but if i can just get an example of setting one formula up i can repeat the process.

The above example lets say.. i want a formula to look in the end column (let's say J) of that instance. If that column in that instance (J8:J40) has any values that are less than 10 i want to cut the row instance (lets say J8 has a value of less than 10, i want to take cells J1:J8 that have that instance of 10 and cut them under misc.)

I hope this clears it up a little.

 

[KenWright]

OK, without any kind of logic behind the ranges it's hard to write anything that will create them on the fly, so for a hardcoded approach (Which I really struggle to believe has to be the case on some 200 sheets), you can use the following to get started:-

Sub FilterData()

Dim RngIn As Range
Dim RngOut As Range

Set RngIn = Range("H1:H36")
Set RngOut = Range("A40")
Application.ScreenUpdating = False

With RngIn
.AutoFilter Field:=1, Criteria1:="<=10", Operator:=xlAnd
.SpecialCells(xlCellTypeVisible).Copy
RngOut.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.AutoFilter
End With

Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True

End Sub

If you can find some logic behind the ranges then you might want to look at ways of determining the ranges automatically, and to this end you may find the following code examples helpful:-

Determine last used row in Col A:-
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Determine last used row in Col A in a defined sheet:-
LastRw = Sht1.Cells(Rows.Count, "A").End(xlUp).Row

Use the above to set a range down to last row:-
Set Rng = Sht2.Range(Cells(LastRw + 1, "A"), Cells(Rows.Count, "A"))

Find last used Column in usedrange (Not foolproof)
ColW = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

Find last row in usedrange (Not foolproof):-
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

The reason I say the last two are not foolproof is that the usedrange can sometimes return erroneous results, and Excel can often think it is larger than it is.

For running through multiple sheets:-

Dim wkSht As Worksheet

For Each wkSht In Worksheets
With wkSht
If blah blah blah then
do this
End If
End With
Next wkSht

End Sub

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------