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

--
crowdx4
-----------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774
View this thread: http://www.excelforum.com/showthread.php?threadid=57365

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" wrote in
message ...

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 :

--
crowdx4
-----------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774
View this thread: http://www.excelforum.com/showthread.php?threadid=57365

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

--
crowdx4
-----------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774
View this thread: http://www.excelforum.com/showthread.php?threadid=57365

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" wrote in
message ...

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" wrote in
message ...

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" wrote in
message ...

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 :

--
crowdx4
-----------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774
View this thread: http://www.excelforum.com/showthread.php?threadid=57365