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

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

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, 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, 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(datevalue("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

Is it perhaps running in one of your worksheet/workbook events - for
instance Worksheet_SelectionChange or something similar?

Jeff

"GregR" <gregrivet@gmail.com> wrote in message
news:1149261874.647230.128150@g10g2000cwb.googlegroups.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
>

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" <gregrivet@gmail.com> wrote in message
> news:1149261874.647230.128150@g10g2000cwb.googlegroups.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
> >

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)),.range("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(datevalue("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

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(datevalue("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

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(datevalue("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

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)),.range("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(datevalue("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

Dave, thank you very much. Got it all working now.

Greg
Dave Peterson wrote:
> 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(datevalue("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

I'm not sure if this was posted before or after the "got it working" post.

But I don't understand why you would reverse the row and column variables.

GregR wrote:
>
> 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)),.range("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(datevalue("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

--

Dave Peterson

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(datevalue("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

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(datevalue("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