Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Past Special- Transpose Function? | Excel Discussion (Misc queries) | |||
past special | Excel Worksheet Functions | |||
Past Special Concatenated formula | Excel Discussion (Misc queries) | |||
automated copy and past special feature | Excel Programming | |||
Past Special changes results for macros... how to prevent this | Excel Programming |