Macro Modification help

[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

 

[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

 

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

 

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