Macro

[puiuluipui]

Hi, i have this line of code that copy 6 cells to the right. Can this be
modified to copy the cell before those 6 cells too?

dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1
c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a")
Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name

The word this macro is searching for is in B and this is why copied range is
B:G
This code copy from range B:G.

Can this code be made to copy from range A:G?
Thanks!!

 

[Mike H]

Hi,

This time we need the offset function

c.Offset(-1).Resize(2, 7).Copy

I assume that c is a Range object so imagine C is actually A10.

We offset -1 row to A9 then we resize by 2 rows and it now becomes A9:A10
and then by 7 columns to achieve what you want


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[puiuluipui]

Hi, is not working. I haven't explained exactly the first time. Below is the
entire code. I guess is easy for you to see the entire code than my
explanation.

The code copy 6 cells starting from B column and display them in "search"
sheet. The seven cell displayed in search sheet is the sheet's name from
where cells were copied. The macro ignore from search, sheets : planning and
search.

This is the entire code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$H$1" Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sheets("Search")
lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 4)
..Range("a3:g" & lr).ClearContents
what = UCase(.Range("H1"))
On Error Resume Next
mydays = Array("LUNI", "MARTI", "MIERCURI", "JOI", "VINERI")
For Each sh In mydays

With Worksheets(sh).Range("b5:b1000")
Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do

dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row + 1
c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a")
Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Next sh
lr = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("a3:g" & lr).Borders.LineStyle = xlNone
End With
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub


Thanks!!

 

[puiuluipui]

Hi, the first of this 6 cells is the found one.

The found cell is in B column, so the macro copy from B to G. (6 cells)
I need the macro to copy from A to G. (7cells)
I need the macro to copy with one cell before the found one. so, the macro
to copy 7 cells.

Thanks!!

 

[Mike H]

Hi,

OK so you find some value in column B and then copy columns A to G of that
row. What you want to do if I understand correctly is coppy A to G of the row
you find AND A to g of the previous row. is that correct? If so i gave you
the correct answer last time.

Replace this row
c.Resize(, 7).Copy Sheets("Search").Cells(dlr, "a")

With this one
c.Offset(-1).Resize(2, 7).Copy Sheets("Search").Cells(dlr, "a")

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[puiuluipui]

Hi,
What i want to do is copy A to G of the row i find. This is all.
I don't want another row.
If i search for "John" and the macro finds "John" in B5, then the macro to
copy A5:G5.
I tried your ideea and it's fine, but although copy the row above, doesn't
copy from A to G. It copy from B to H.

Thanks!

 

[Mike H]

Hi,

Now I understand. This is the correct line

c.Offset(, -1).Resize(, 7).Copy Sheets("Search").Cells(dlr, "a")

Note this steps 1 column to the left c.Offset(, -1) and then resizes to 7
columns from column A so A to g are copied

But now there's a problem because in the next line of your code

Sheets("search").Cells(dlr, "g") = Worksheets(sh).Name

You write the sheet name to column G which will overwrite the last cell you
just copied. You will have to copy 6 columns or put the sheet name somewhere
else (say) column H
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[puiuluipui]

This is exactly what i needed.
Excellent work! Thanks!

I have one more question. Is there any way i can insert in this macro some
names that will belong to certain groups?

Ex:

Group 1
John
Mary

Group 2
Sam
George
Jim

What i need:
If i search John, then the result to be all rows containing "John"
If i search Group 1, than the result to be all rows containing "John" and
all rows containing "Mary"
The same with group 2. If i search by name to display only rows containing
that name. if i search by group, than the macro to display rows containing
all names in that group.

This thing will be so great.
Can this be done?
Thanks!!!