VBA code to replace Past Special

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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.
 
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
 
Thanks for the information. One question, does this replace the formula in
cell B1, because I'm not sure what the w2 means?
 
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
 
Back
Top