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