Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Modification help
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 ------------------------------------------- Sub Fixit1() 'Use if event macro stops working Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Modification help
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 ------------------------------------------- Sub Fixit1() 'Use if event macro stops working Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Modification help
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" wrote: 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 ------------------------------------------- Sub Fixit1() 'Use if event macro stops working Application.EnableEvents = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Modification help
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. "Jim Cone" wrote: 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 ------------------------------------------- Sub Fixit1() 'Use if event macro stops working Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro - modification | Excel Discussion (Misc queries) | |||
Macro modification | Excel Discussion (Misc queries) | |||
Macro modification | Excel Programming | |||
Macro Modification Help | Excel Programming | |||
Need Macro Modification | Excel Programming |