|
Resize Problem
I have this code which errors on the resize line
Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg |
Resize Problem
The .resize(0,1) says to resize that single cell to a range of 0 rows by 1
column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson |
Resize Problem
Dave, I'm glad someone knows what I want. One more question, when I
record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson |
Resize Problem
Are you running a macro called Update (maybe by clicking a button or from a
toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson |
Resize Problem
Dave, I do have a macro called Update, but I have not set it in motion
or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson |
Resize Problem
Dave, another small problem, I have this formula that finds the
intersection of a row/col and returns a value. I want to move the value at this address one column to the right. It is part of this macro above. How do I do it or what is the VBA solution? The formula is: Index($A$1:$A$Z100,Match("Cap",A1:A100,0),match(da tevalue("5/1/2006"),A1:AZ1,0)). TIA Greg GregR wrote: Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson |
Resize Problem
Is it perhaps running in one of your worksheet/workbook events - for
instance Worksheet_SelectionChange or something similar? Jeff "GregR" wrote in message oups.com... Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson |
Resize Problem
Jeff, I don't see any WS/WB events in the WB, but it is a good point.
I'll check next time I record a macro. Thanks Greg Jeff Standen wrote: Is it perhaps running in one of your worksheet/workbook events - for instance Worksheet_SelectionChange or something similar? Jeff "GregR" wrote in message oups.com... Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson |
Resize Problem
You want to move the value that's returned in that =index() formula one cell to
the right of where it was found? dim myRow as variant 'may return an error dim myCol as variant with worksheets("whateverthenameishere") myrow = application.match("cap", .range("a1:a100"),0) mycol = application.match(clng(dateserial(2006,5,1)),.rang e("a1:az1"),0) if iserror(myrow) _ or iserror(mycol) then msgbox "missing at least one match!" else .cells(myrow,mycol+1).value = .cells(myrow,mycol).value end if end with (Untested, uncompiled. Watch out for typos.) GregR wrote: Dave, another small problem, I have this formula that finds the intersection of a row/col and returns a value. I want to move the value at this address one column to the right. It is part of this macro above. How do I do it or what is the VBA solution? The formula is: Index($A$1:$A$Z100,Match("Cap",A1:A100,0),match(da tevalue("5/1/2006"),A1:AZ1,0)). TIA Greg GregR wrote: Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Resize Problem
Dave, just to clarify. The formula is actually in another cell and
returns the intersection value. I want to move the value at the intersection to the right, not where the returned formula is located. TIA Greg GregR wrote: Dave, another small problem, I have this formula that finds the intersection of a row/col and returns a value. I want to move the value at this address one column to the right. It is part of this macro above. How do I do it or what is the VBA solution? The formula is: Index($A$1:$A$Z100,Match("Cap",A1:A100,0),match(da tevalue("5/1/2006"),A1:AZ1,0)). TIA Greg GregR wrote: Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson |
Resize Problem
That code actually copied the value--it didn't clear out the original cell.
If you want to clear out the original cell, add a line after this: ..cells(myrow,mycol+1).value = .cells(myrow,mycol).value ..cells(myrow,mycol).value = "" GregR wrote: Dave, just to clarify. The formula is actually in another cell and returns the intersection value. I want to move the value at the intersection to the right, not where the returned formula is located. TIA Greg GregR wrote: Dave, another small problem, I have this formula that finds the intersection of a row/col and returns a value. I want to move the value at this address one column to the right. It is part of this macro above. How do I do it or what is the VBA solution? The formula is: Index($A$1:$A$Z100,Match("Cap",A1:A100,0),match(da tevalue("5/1/2006"),A1:AZ1,0)). TIA Greg GregR wrote: Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Resize Problem
Dave, after I reversed your Row/Col ref, I didn't receive any errors,
but the value wasn't copied or moved either to the next column? Greg Dave Peterson wrote: You want to move the value that's returned in that =index() formula one cell to the right of where it was found? dim myRow as variant 'may return an error dim myCol as variant with worksheets("whateverthenameishere") myrow = application.match("cap", .range("a1:a100"),0) mycol = application.match(clng(dateserial(2006,5,1)),.rang e("a1:az1"),0) if iserror(myrow) _ or iserror(mycol) then msgbox "missing at least one match!" else .cells(myrow,mycol+1).value = .cells(myrow,mycol).value end if end with (Untested, uncompiled. Watch out for typos.) GregR wrote: Dave, another small problem, I have this formula that finds the intersection of a row/col and returns a value. I want to move the value at this address one column to the right. It is part of this macro above. How do I do it or what is the VBA solution? The formula is: Index($A$1:$A$Z100,Match("Cap",A1:A100,0),match(da tevalue("5/1/2006"),A1:AZ1,0)). TIA Greg GregR wrote: Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Resize Problem
Woohoo!!!
GregR wrote: Dave, as usual, it's because I didn't understand. When I went back to your original, everything worked. I'll slooooooooooooooooooooooooowly get there someday. Once again, thanks a bunch Greg GregR wrote: Dave, just to clarify. The formula is actually in another cell and returns the intersection value. I want to move the value at the intersection to the right, not where the returned formula is located. TIA Greg GregR wrote: Dave, another small problem, I have this formula that finds the intersection of a row/col and returns a value. I want to move the value at this address one column to the right. It is part of this macro above. How do I do it or what is the VBA solution? The formula is: Index($A$1:$A$Z100,Match("Cap",A1:A100,0),match(da tevalue("5/1/2006"),A1:AZ1,0)). TIA Greg GregR wrote: Dave, I do have a macro called Update, but I have not set it in motion or clicked a button to activate it, not sure what is happening. Greg Dave Peterson wrote: Are you running a macro called Update (maybe by clicking a button or from a toolbar)?? If you are, then stop recording before you click that button. GregR wrote: Dave, I'm glad someone knows what I want. One more question, when I record a macro after every recorded line, I get a line Application.run "Update" What is that and how do I stop it. TIA Greg Dave Peterson wrote: The .resize(0,1) says to resize that single cell to a range of 0 rows by 1 column. That doesn't exist. Maybe you meant .resize(1,2) (one row by 2 columns???) I think I'd add just a bit of a check: Dim FoundCell As Range Set FoundCell = Cells.Find(what:="Pending", after:=ActiveCell, _ LookIn:=xlFormulas, lookat:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then MsgBox "not found" Else If FoundCell.Column = ActiveSheet.Columns.Count Then MsgBox "nothing to the right!" Else FoundCell.Resize(1, 2).EntireColumn.Cut FoundCell.Offset(0, 3).EntireColumn.Insert Shift:=xlToRight End If End If And I think you meant .offset(0,3) for the insertion, too. But test it to see if it does what you want. GregR wrote: I have this code which errors on the resize line Cells.Find(what:="Pending", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate ActiveCell.Resize(0, 1).Select Selection.EntireColumn.Cut ActiveCell.Offset(0, 1).Select Selection.Insert Shift:=xlToRight What the desired result is, look for cell with "Pending", select that cell and the one to the right, cut both those columns and move them one to the right. TIA Greg -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 02:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com