copy and paste with if

[headache]

hi guys,
I am a VB beginner and tryed to find the solution in the search tool but I haven't, plus I am not english speaker, so sorry for my syntax

Anyway, here is my problem:

sheet (1)
A1: Robert
A2: 1
A3: 6

sheet (2) is my database

A B C D
reference analyst Pick up Booking
product 1, 2... robert, bill... 1,2,3... 5,10,14...

I want a code that replies to these conditions:
- name in the sheets(2), column B, cells B1, B2 etc until the end of the column, equal A1 (ie. "Robert")
- pick up in column C is greater than A2 (ie. 1)
- booking in colum D is less than or equal to A3 (ie.6)

For the products that match all these 3 conditions, I want to copy their references (cell in column A) and to paste them in sheet (1), A4.

I don't want to have blank cells below sheet (1), A4. I say that because first, I did a simple if(and) formula, but then I had some suites of 100 blanks cells and then 1 reference that matches the 3 conditions, then again 55 blanks cells and another ref etc, so not convenient to use even with a filter

I hope I have been clearer enough and will be delighted if you can solve that for me
Cheers mates

[mdmackillop]

Can you post a sample workbook? Use Manage Attachments in the Go Advanced reply section.

[headache]

thanks mdmackillop

I attached the spreadsheet that explains what I am trying to do

pls let me know if you want more explanations it will be fantastic if you could sort it out for me
cheers

[georgiboy]

This should do it

For one button

[vba]Sub FindLow()
Dim MyRange As Range, rCell As Range
Dim SheetName As String, x As Integer

SheetName = Sheets("change").Range("D8").Value

Set MyRange = Sheets(SheetName).Range("H1:H" & Sheets(SheetName).Range("H" & Rows.Count).End(xlUp).Row)

x = 18

For Each rCell In MyRange.Cells

If rCell.Value = Sheets("change").Range("D6").Value Then
If rCell.Offset(, 3).Value < Sheets("change").Range("D13").Value Then
Sheets("change").Range("D" & x).Value = rCell.Offset(, -7).Value
x = x + 1
End If
End If

Next rCell

End Sub
[/vba]
and for the other

[vba]Sub FindHi()
Dim MyRange As Range, rCell As Range
Dim SheetName As String, x As Integer

SheetName = Sheets("change").Range("D8").Value

Set MyRange = Sheets(SheetName).Range("H1:H" & Sheets(SheetName).Range("H" & Rows.Count).End(xlUp).Row)

x = 18

For Each rCell In MyRange.Cells

If rCell.Value = Sheets("change").Range("D6").Value Then
If rCell.Offset(, 3).Value > Sheets("change").Range("G13").Value Then
If rCell.Offset(, -3).Value <> "Brasil" Then
Sheets("change").Range("G" & x).Value = rCell.Offset(, -7).Value
x = x + 1
End If
End If
End If

Next rCell

End Sub
[/vba]
Hope this helps

[headache]

what a good forum!
Perfect reply in less than 1 hour, cheers georgiboys
It works very well!!!
Just a detail, if I tried to copy and paste the segments that looked up, I can't do a ctrl+shift+down arrow to select up to the last segment.

On the example there are only few lines, but 10.000 in reality, so quite a lots of segments could pop up and that would be great if I won't have to select them with the mouse.
Any idea?

Many thanks anyway for your great help

[georgiboy]

Just a detail, if I tried to copy and paste the segments that looked up, I can't do a ctrl+shift+down arrow to select up to the last segment.

i have no problem selecting the results with this method

[headache]

sorry georgiboy, it's working perfectly well
many thanks mate

[headache]

guys, I need your help again.
On the same file than above, I actually want to add another condition, and this condition is that values in column M are below 4.
I wrote the following code:

If rCell.Offset(, 5).Value < "4" Then

And add a "end if," but I have an error "run time error 1004"

Is it something related to the range defined at the beginning of georgiboy code?

Does that make sense or do you need the spreadsheet?
cheers

[georgiboy]

You may want to remove the "" from "4" and see how you get on but if this does not work then post what you have so far.