Macro Modification help

A

akemeny

How can I modify the following Macro so that it will look at the value of the
cell to find the word? Right now its set to look at the cell, but the cells
are linked to other sheets.

Private Sub Worksheet_Activate()
Call Sheet4.getvalues
End Sub
--------------------------------------------------
Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 2 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "ag") = overturned Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "ag") = overturned And Not IsDate(.Cells(i, "bm")) Then
..Rows(i).Copy Rows(dlr)
Next i
End With
End Sub
 
J

Jim Cone

Have you declared all of your variables?
Are you using "Option Explicit"
What is "overturned"?
--
Jim Cone
Portland, Oregon USA



"akemeny"
wrote in message
How can I modify the following Macro so that it will look at the value of the
cell to find the word? Right now its set to look at the cell, but the cells
are linked to other sheets.

Private Sub Worksheet_Activate()
Call Sheet4.getvalues
End Sub
--------------------------------------------------
Sub getvalues()
lr = Application.Max(2, Cells(Rows.Count, 1).End(xlUp).Row)
'MsgBox lr
Rows("2:" & lr).ClearContents
With Worksheets("Master")
slr = .Cells(Rows.Count, "c").End(xlUp).Row
'MsgBox slr
For i = 2 To slr
dlr = Cells(Rows.Count, "a").End(xlUp).Row + 1
' If .Cells(i, "ag") = overturned Then .Rows(i).Copy Rows(dlr)
If .Cells(i, "ag") = overturned And Not IsDate(.Cells(i, "bm")) Then
..Rows(i).Copy Rows(dlr)
Next i
End With
End Sub
 
A

akemeny

Well I'm not really sure what you mean by variables. Overturned is the word
that I want the macro to look for in that cell. If it finds the word
overturned, but there hasn't been a date placed in column BM then I need it
to copy over to the spreadsheet that contains the macro. The set up works
for everything else that I'm using, but I need it to look in the values of
the cell rather than read the link of the cell.
 
J

Jim Cone

lr, slr, i, dlr are your variables.
(it's time to read chapter one again)

You can try...
If .Cells(i, "ag").Text = "overturned"
(note the quote marks around overturned)
--
Jim Cone
Portland, Oregon USA



"akemeny"
wrote in message
Well I'm not really sure what you mean by variables. Overturned is the word
that I want the macro to look for in that cell. If it finds the word
overturned, but there hasn't been a date placed in column BM then I need it
to copy over to the spreadsheet that contains the macro. The set up works
for everything else that I'm using, but I need it to look in the values of
the cell rather than read the link of the cell.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top