VBA code to replace Past Special
 

I have a range of cells (ie. A1:A5) which can contain names. In cell B1:B5.
I use VLOOKUP to find the name in a table, and insert the value to the right
of the name in the table into the active cell, (ie B1). The only problem is
that later, the user will want to copy and paste the value in cell B1 to
somewhere else on the spreedsheet. I can have the user copy and past
special, but this requires too much thought on the part of the user.

I'm looking for some code that will take the active cell, (B1), and say:
Use the name to the left of the active cell,
Find the name in the table,
Take the value to the right of the name in this table
Paste in into cell B1 as a value, (not a formula).

active cell table
A1 B1 C1 D1
-------------------------------------------------------------
Jones 101 Jones 101
Smith 201
White 301

Now the user could copy and paste without using past special.
--
Howard

VBA code to replace Past Special
 

something simplistic to replace the formula...

for rw = 1 to 10
if cells(rw,2)<"" then
cells(rw,2).Value = cells(rw,2).Value
next



"Howard" wrote:

I have a range of cells (ie. A1:A5) which can contain names. In cell B1:B5.
I use VLOOKUP to find the name in a table, and insert the value to the right
of the name in the table into the active cell, (ie B1). The only problem is
that later, the user will want to copy and paste the value in cell B1 to
somewhere else on the spreedsheet. I can have the user copy and past
special, but this requires too much thought on the part of the user.

I'm looking for some code that will take the active cell, (B1), and say:
Use the name to the left of the active cell,
Find the name in the table,
Take the value to the right of the name in this table
Paste in into cell B1 as a value, (not a formula).

active cell table
A1 B1 C1 D1
-------------------------------------------------------------
Jones 101 Jones 101
Smith 201
White 301

Now the user could copy and paste without using past special.
--
Howard

VBA code to replace Past Special
 

Thanks for the information. One question, does this replace the formula in
cell B1, because I'm not sure what the w2 means?
--
Howard


"Patrick Molloy" wrote:

something simplistic to replace the formula...

for rw = 1 to 10
if cells(rw,2)<"" then
cells(rw,2).Value = cells(rw,2).Value
next



"Howard" wrote:

I have a range of cells (ie. A1:A5) which can contain names. In cell B1:B5.
I use VLOOKUP to find the name in a table, and insert the value to the right
of the name in the table into the active cell, (ie B1). The only problem is
that later, the user will want to copy and paste the value in cell B1 to
somewhere else on the spreedsheet. I can have the user copy and past
special, but this requires too much thought on the part of the user.

I'm looking for some code that will take the active cell, (B1), and say:
Use the name to the left of the active cell,
Find the name in the table,
Take the value to the right of the name in this table
Paste in into cell B1 as a value, (not a formula).

active cell table
A1 B1 C1 D1
-------------------------------------------------------------
Jones 101 Jones 101
Smith 201
White 301

Now the user could copy and paste without using past special.
--
Howard

VBA code to replace Past Special
 

I'm not sure what you're asking
If a cell contains a formula then

.Value = = .Value

will replace the formula in a cell by its calculated value

Option Explicit
Sub DoStuff()
Dim rw As Long
For rw = 1 To 100
If Cells(rw, 1) < "" Then
With Cells(rw, 2)
.FormulaR1C1 = "=VLOOKUP(RC1,R1C3:R100C4,2,False)"
.Value = .Value
End With
End If
Next
End Sub







"Howard" wrote:

Thanks for the information. One question, does this replace the formula in
cell B1, because I'm not sure what the w2 means?
--
Howard


"Patrick Molloy" wrote:

something simplistic to replace the formula...

for rw = 1 to 10
if cells(rw,2)<"" then
cells(rw,2).Value = cells(rw,2).Value
next



"Howard" wrote:

I have a range of cells (ie. A1:A5) which can contain names. In cell B1:B5.
I use VLOOKUP to find the name in a table, and insert the value to the right
of the name in the table into the active cell, (ie B1). The only problem is
that later, the user will want to copy and paste the value in cell B1 to
somewhere else on the spreedsheet. I can have the user copy and past
special, but this requires too much thought on the part of the user.

I'm looking for some code that will take the active cell, (B1), and say:
Use the name to the left of the active cell,
Find the name in the table,
Take the value to the right of the name in this table
Paste in into cell B1 as a value, (not a formula).

active cell table
A1 B1 C1 D1
-------------------------------------------------------------
Jones 101 Jones 101
Smith 201
White 301

Now the user could copy and paste without using past special.
--
Howard

VBA code to replace Past Special
 

This reply looks like it answers the question. Thanks again for you help.
--
Howard


"Patrick Molloy" wrote:

I'm not sure what you're asking
If a cell contains a formula then

.Value = = .Value

will replace the formula in a cell by its calculated value

Option Explicit
Sub DoStuff()
Dim rw As Long
For rw = 1 To 100
If Cells(rw, 1) < "" Then
With Cells(rw, 2)
.FormulaR1C1 = "=VLOOKUP(RC1,R1C3:R100C4,2,False)"
.Value = .Value
End With
End If
Next
End Sub







"Howard" wrote:

Thanks for the information. One question, does this replace the formula in
cell B1, because I'm not sure what the w2 means?
--
Howard


"Patrick Molloy" wrote:

something simplistic to replace the formula...

for rw = 1 to 10
if cells(rw,2)<"" then
cells(rw,2).Value = cells(rw,2).Value
next



"Howard" wrote:

I have a range of cells (ie. A1:A5) which can contain names. In cell B1:B5.
I use VLOOKUP to find the name in a table, and insert the value to the right
of the name in the table into the active cell, (ie B1). The only problem is
that later, the user will want to copy and paste the value in cell B1 to
somewhere else on the spreedsheet. I can have the user copy and past
special, but this requires too much thought on the part of the user.

I'm looking for some code that will take the active cell, (B1), and say:
Use the name to the left of the active cell,
Find the name in the table,
Take the value to the right of the name in this table
Paste in into cell B1 as a value, (not a formula).

active cell table
A1 B1 C1 D1
-------------------------------------------------------------
Jones 101 Jones 101
Smith 201
White 301

Now the user could copy and paste without using past special.
--
Howard