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

 

[Simon Lloyd]

If this copies 6 cells to the right and you want to copy the cel
before it, does that mean you want to copy the target cell too? so tha
would be the found cell and the 6 cells to the right, is that correct



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

VBA Code:
--------------------
dlr = Sheets("Search").Cells(Rows.Count, "a").End(xlUp).Row +
c.resize(, 7).Copy Sheets("Search").Cells(dlr, "a"
Sheets("search").Cells(dlr, "g") = Worksheets(sh).Nam

--------------------
The word this macro is searching for is in B and this is why copie
range i
B:
This code copy from range B:G

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


--
Simon Lloyd

Regards
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)

 

[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!!!