Is there a way to loop thru a list of names and to assign a specific value to
them.

In C20:C1022 is my current list of names and we add to this list everyday. I
want to add a Vlookup formula to the adjasent cell in column D if and only if
a name is present in column C. (=VLOOKUP(R[0]C[-1],RepInfo,4)

Now the twist, after the cell has been assigned a value by the macro, I want
to eliminate the formula and leave the value. Once this is done I want to
move to the next cell and repeat.

The workbook I am doing this in can not have formula inbedded in it because
it is prone to manipulation by other users where one inserted cell the wrong
way will throw all formulas off and the tracking information will not be
accurate.

Any suggestions,
thanks
Peter

One way:

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(3).Rows
If rCell.FormulaR1C1 < "" Then
rCell.Offset(0, 1).Value = Evaluate("=IF(ISNA(VLOOKUP(C" & rCell.Row
& ",RepInfo,4,False))," & """" & """" & ",VLOOKUP(C" & rCell.Row &
",RepInfo,4,False))")
End If
Next rCell

Watch for line wrapping in the reader!

HTH

"Looping through" wrote:

Is there a way to loop thru a list of names and to assign a specific value to
them.

In C20:C1022 is my current list of names and we add to this list everyday. I
want to add a Vlookup formula to the adjasent cell in column D if and only if
a name is present in column C. (=VLOOKUP(R[0]C[-1],RepInfo,4)

Now the twist, after the cell has been assigned a value by the macro, I want
to eliminate the formula and leave the value. Once this is done I want to
move to the next cell and repeat.

The workbook I am doing this in can not have formula inbedded in it because
it is prone to manipulation by other users where one inserted cell the wrong
way will throw all formulas off and the tracking information will not be
accurate.

Any suggestions,
thanks
Peter

Wow, that worked great. Thank you. Would there be a way to make this code run
automatically and specifically for the row the user is currently on based on
criteria inputed in to the first two cell of that row. (I.E. Lets say I was
entering info into row 200; once A200 has a date entered and B200 has the
quote number assigned and C200 has the rep name, run the code to insert the
formula and return the value needed?)

If not this works fine and I can use it as is.
Thanks
Peter

"XP" wrote:


One way:

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(3).Rows
If rCell.FormulaR1C1 < "" Then
rCell.Offset(0, 1).Value = Evaluate("=IF(ISNA(VLOOKUP(C" & rCell.Row
& ",RepInfo,4,False))," & """" & """" & ",VLOOKUP(C" & rCell.Row &
",RepInfo,4,False))")
End If
Next rCell

Watch for line wrapping in the reader!

HTH

"Looping through" wrote:

Is there a way to loop thru a list of names and to assign a specific value to
them.

In C20:C1022 is my current list of names and we add to this list everyday. I
want to add a Vlookup formula to the adjasent cell in column D if and only if
a name is present in column C. (=VLOOKUP(R[0]C[-1],RepInfo,4)

Now the twist, after the cell has been assigned a value by the macro, I want
to eliminate the formula and leave the value. Once this is done I want to
move to the next cell and repeat.

The workbook I am doing this in can not have formula inbedded in it because
it is prone to manipulation by other users where one inserted cell the wrong
way will throw all formulas off and the tracking information will not be
accurate.

Any suggestions,
thanks
Peter

Another way is to plop the formula into all the cells in the range in one fell
swoop. Then clean it up.

Dim myRng As Range
Dim wks As Worksheet
Set wks = Worksheets("somesheethere")
With wks
Set myRng = .Range("d20:d" & .Cells(.Rows.Count, "C").End(xlUp).Row)
With myRng
'plop in the common formula
.FormulaR1C1 = "=vlookup(rc[-1],repinfo,4,0)"

'convert to values
.Value = .Value

'just in case there are no errors
On Error Resume Next
'clean up any errors
.Cells.SpecialCells(xlCellTypeConstants, xlErrors).ClearContents
On Error GoTo 0
End With
End With

I changed the =vlookup() to look for an exact match (4th parm = 0 or False). I
figured with names, you'd want an exact match.


Looping through wrote:

Is there a way to loop thru a list of names and to assign a specific value to
them.

In C20:C1022 is my current list of names and we add to this list everyday. I
want to add a Vlookup formula to the adjasent cell in column D if and only if
a name is present in column C. (=VLOOKUP(R[0]C[-1],RepInfo,4)

Now the twist, after the cell has been assigned a value by the macro, I want
to eliminate the formula and leave the value. Once this is done I want to
move to the next cell and repeat.

The workbook I am doing this in can not have formula inbedded in it because
it is prone to manipulation by other users where one inserted cell the wrong
way will throw all formulas off and the tracking information will not be
accurate.

Any suggestions,
thanks
Peter

--

Dave Peterson

Right click on the sheet tab and select "View Code", then copy the following
into that module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(ActiveCell.Row, 1).FormulaR1C1 < "" And _
Cells(ActiveCell.Row, 2).FormulaR1C1 < "" And _
Cells(ActiveCell.Row, 3).FormulaR1C1 < "" Then
Cells(ActiveCell.Row, 4).Value = Evaluate("=IF(ISNA(VLOOKUP(C" &
ActiveCell.Row & ",RepInfo,4,False))," & """" & """" & ",VLOOKUP(C" &
ActiveCell.Row & ",RepInfo,4,False))")
End If
Calculate
End Sub

HTH

"Looping through" wrote:

Wow, that worked great. Thank you. Would there be a way to make this code run
automatically and specifically for the row the user is currently on based on
criteria inputed in to the first two cell of that row. (I.E. Lets say I was
entering info into row 200; once A200 has a date entered and B200 has the
quote number assigned and C200 has the rep name, run the code to insert the
formula and return the value needed?)

If not this works fine and I can use it as is.
Thanks
Peter

"XP" wrote:


One way:

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(3).Rows
If rCell.FormulaR1C1 < "" Then
rCell.Offset(0, 1).Value = Evaluate("=IF(ISNA(VLOOKUP(C" & rCell.Row
& ",RepInfo,4,False))," & """" & """" & ",VLOOKUP(C" & rCell.Row &
",RepInfo,4,False))")
End If
Next rCell

Watch for line wrapping in the reader!

HTH

"Looping through" wrote:

Is there a way to loop thru a list of names and to assign a specific value to
them.

In C20:C1022 is my current list of names and we add to this list everyday. I
want to add a Vlookup formula to the adjasent cell in column D if and only if
a name is present in column C. (=VLOOKUP(R[0]C[-1],RepInfo,4)

Now the twist, after the cell has been assigned a value by the macro, I want
to eliminate the formula and leave the value. Once this is done I want to
move to the next cell and repeat.

The workbook I am doing this in can not have formula inbedded in it because
it is prone to manipulation by other users where one inserted cell the wrong
way will throw all formulas off and the tracking information will not be
accurate.

Any suggestions,
thanks
Peter

XP, Thank you, this works great.

Peter

"XP" wrote:

Right click on the sheet tab and select "View Code", then copy the following
into that module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Cells(ActiveCell.Row, 1).FormulaR1C1 < "" And _
Cells(ActiveCell.Row, 2).FormulaR1C1 < "" And _
Cells(ActiveCell.Row, 3).FormulaR1C1 < "" Then
Cells(ActiveCell.Row, 4).Value = Evaluate("=IF(ISNA(VLOOKUP(C" &
ActiveCell.Row & ",RepInfo,4,False))," & """" & """" & ",VLOOKUP(C" &
ActiveCell.Row & ",RepInfo,4,False))")
End If
Calculate
End Sub

HTH

"Looping through" wrote:

Wow, that worked great. Thank you. Would there be a way to make this code run
automatically and specifically for the row the user is currently on based on
criteria inputed in to the first two cell of that row. (I.E. Lets say I was
entering info into row 200; once A200 has a date entered and B200 has the
quote number assigned and C200 has the rep name, run the code to insert the
formula and return the value needed?)

If not this works fine and I can use it as is.
Thanks
Peter

"XP" wrote:


One way:

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(3).Rows
If rCell.FormulaR1C1 < "" Then
rCell.Offset(0, 1).Value = Evaluate("=IF(ISNA(VLOOKUP(C" & rCell.Row
& ",RepInfo,4,False))," & """" & """" & ",VLOOKUP(C" & rCell.Row &
",RepInfo,4,False))")
End If
Next rCell

Watch for line wrapping in the reader!

HTH

"Looping through" wrote:

Is there a way to loop thru a list of names and to assign a specific value to
them.

In C20:C1022 is my current list of names and we add to this list everyday. I
want to add a Vlookup formula to the adjasent cell in column D if and only if
a name is present in column C. (=VLOOKUP(R[0]C[-1],RepInfo,4)

Now the twist, after the cell has been assigned a value by the macro, I want
to eliminate the formula and leave the value. Once this is done I want to
move to the next cell and repeat.

The workbook I am doing this in can not have formula inbedded in it because
it is prone to manipulation by other users where one inserted cell the wrong
way will throw all formulas off and the tracking information will not be
accurate.

Any suggestions,
thanks
Peter