Find a cell and then insert a formula into adjacent cell

[crowdx42]

Ok, so the last thing I want to do on this preoject is find a cell wit
a name in it and then insert a vloolup in a cell 4 columns to th
rights.
I have tried the code below but can't figure out how to move the inser
point from the found cell over to the column that I want to insert th
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 pe
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 Su

 

[Gary Keramidas]

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"

 

[crowdx42]

Exellent,,, worked like a charm :

 

[crowdx42]

Ok, so I just want to copy the name from a worksheet into the cell A1,
need this to work relative across 20 worksheets. Also in the same macr
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 .xl
at the end of the name.
Any help gratefully appreciated.
Patric

 

[Gary Keramidas]

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 Keramidas]

sorry, you wanted sheet name, not workbook name

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

 

[Norman Jones]

Hi Patrick,

See response(s) to your later thtread.

 

[crowdx42]

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