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

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

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

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