Find a cell and then insert a formula into adjacent cell

Ok, so the last thing I want to do on this preoject is find a cell with a name in it and then insert a vloolup in a cell 4 columns to the rights.
I have tried the code below but can't figure out how to move the insert point from the found cell over to the column that I want to insert the formula.
Thanks for all the help
Patrick

Sub Insert_VLOOKUP()
Dim Findfirst As Object, FindNext As Object, FindNext2 As Object
Set Findfirst = Cells.Find(What:="CARDS", LookIn:=xlValues)
If Not Findfirst Is Nothing Then
Findfirst.Select
With Range("A" & Findfirst.Row & ":F" & Findfirst.Row).Borders(xlEdgeTop)
ActiveCell.FormulaR1C1 =_
"=VLOOKUP(RC[-5],'Product per Call'!R[-2]C[-5]:R[484]C[10],16,FALSE)"
End With
Set FindNext2 = Findfirst
Do
Set FindNext = Cells.FindNext(After:=FindNext2)
If Not FindNext Is Nothing Then
With Range("A" & FindNext.Row & ":F" & FindNext.Row).Borders(xlEdgeTop)
ActiveCell.FormulaR4C4 =_
"=VLOOKUP(RC[-5],'Product'!R[-2]C[-5]:R[484]C[10],16,FALSE)"
End With
End If
Set FindNext2 = FindNext
FindNext2.Interior.ColorIndex = 0
FindNext2.Select
Loop Until FindNext.Address = Findfirst.Address
End With
End Sub

have you tried offset?

activecell.offset(,1) will acees the cell to the right of the activecell

so if the active cell was A1
this would put test in b1

ActiveCell.Offset(, 1).Value = "test"

--


Gary


"crowdx42" <crowdx42.2cvjep_1156137005.0505@excelforum-nospam.com> wrote in
message news:crowdx42.2cvjep_1156137005.0505@excelforum-nospam.com...
>
> Ok, so the last thing I want to do on this preoject is find a cell with
> a name in it and then insert a vloolup in a cell 4 columns to the
> rights.
> I have tried the code below but can't figure out how to move the insert
> point from the found cell over to the column that I want to insert the
> formula.
> Thanks for all the help
> Patrick
>
> Sub Insert_VLOOKUP()
> Dim Findfirst As Object, FindNext As Object, FindNext2 As Object
> Set Findfirst = Cells.Find(What:="CARDS", LookIn:=xlValues)
> If Not Findfirst Is Nothing Then
> Findfirst.Select
> With Range("A" & Findfirst.Row & ":F" &
> Findfirst.Row).Borders(xlEdgeTop)
> ActiveCell.FormulaR1C1 =_
> "=VLOOKUP(RC[-5],'Product per
> Call'!R[-2]C[-5]:R[484]C[10],16,FALSE)"
> End With
> Set FindNext2 = Findfirst
> Do
> Set FindNext = Cells.FindNext(After:=FindNext2)
> If Not FindNext Is Nothing Then
> With Range("A" & FindNext.Row & ":F" &
> FindNext.Row).Borders(xlEdgeTop)
> ActiveCell.FormulaR4C4 =_
> "=VLOOKUP(RC[-5],'Product'!R[-2]C[-5]:R[484]C[10],16,FALSE)"
> End With
> End If
> Set FindNext2 = FindNext
> FindNext2.Interior.ColorIndex = 0
> FindNext2.Select
> Loop Until FindNext.Address = Findfirst.Address
> End With
> End Sub
>
>
> --
> crowdx42
> ------------------------------------------------------------------------
> crowdx42's Profile:
> http://www.excelforum.com/member.php...o&userid=37749
> View this thread: http://www.excelforum.com/showthread...hreadid=573650
>

Exellent,,, worked like a charm

give this a try

Sub test()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Range("a1").Value = Left(ThisWorkbook.Name, _
Len(ThisWorkbook.Name) - 4)
Next

End Sub


--


Gary


"crowdx42" <crowdx42.2cvmne_1156141204.9587@excelforum-nospam.com> wrote in
message news:crowdx42.2cvmne_1156141204.9587@excelforum-nospam.com...
>
> Ok, so I just want to copy the name from a worksheet into the cell A1, I
> need this to work relative across 20 worksheets. Also in the same macro
> is it possible to delete the last 4 characters in the worksheet name?
> The worksheet was originally named from the file name and so has .xls
> at the end of the name.
> Any help gratefully appreciated.
> Patrick
>
>
> --
> crowdx42
> ------------------------------------------------------------------------
> crowdx42's Profile:
> http://www.excelforum.com/member.php...o&userid=37749
> View this thread: http://www.excelforum.com/showthread...hreadid=573650
>

sorry, you wanted sheet name, not workbook name

ws.Range("a1").Value = Left(ws.Name, Len(ws.Name) - 4)

--


Gary


"crowdx42" <crowdx42.2cvmne_1156141204.9587@excelforum-nospam.com> wrote in
message news:crowdx42.2cvmne_1156141204.9587@excelforum-nospam.com...
>
> Ok, so I just want to copy the name from a worksheet into the cell A1, I
> need this to work relative across 20 worksheets. Also in the same macro
> is it possible to delete the last 4 characters in the worksheet name?
> The worksheet was originally named from the file name and so has .xls
> at the end of the name.
> Any help gratefully appreciated.
> Patrick
>
>
> --
> crowdx42
> ------------------------------------------------------------------------
> crowdx42's Profile:
> http://www.excelforum.com/member.php...o&userid=37749
> View this thread: http://www.excelforum.com/showthread...hreadid=573650
>

Hi Patrick,

See response(s) to your later thtread.


---
Regards,
Norman


"crowdx42" <crowdx42.2cvmne_1156141204.9587@excelforum-nospam.com> wrote in
message news:crowdx42.2cvmne_1156141204.9587@excelforum-nospam.com...
>
> Ok, so I just want to copy the name from a worksheet into the cell A1, I
> need this to work relative across 20 worksheets. Also in the same macro
> is it possible to delete the last 4 characters in the worksheet name?
> The worksheet was originally named from the file name and so has .xls
> at the end of the name.
> Any help gratefully appreciated.
> Patrick
>
>
> --
> crowdx42
> ------------------------------------------------------------------------
> crowdx42's Profile:
> http://www.excelforum.com/member.php...o&userid=37749
> View this thread: http://www.excelforum.com/showthread...hreadid=573650
>

Thank you all so much,,, you are all a great help