If cell in column C = "insert" then OFFSET+CONCATENATE
 

I have a worksheet named "sales" with custom ad codes in Column C and sales
rep initials in Column H. I need to write a macro that will look at Column C
and for every size code = "insert" to offset + concatenate. So for example,
If a reps initials were ABC, for every cell in Column C = "insert" the cell
in the same row, column H should be equal to xxx-"ABC".
Is this possible? (currently, i go through a horrendous process involving
Indirect lookups and a copy/paste from a hidden worksheet -very clumsy even
though it works)

If cell in column C = "insert" then OFFSET+CONCATENATE
 

Try something like:

sub Test()
DIM c as range
DIM r as long, i as long

With Sheets("Sales")
r=.cells(.rows.count, 3).End(xlUp).row
For each c in .range("C2:C" & r) 'assuming there is a header in C1
If c = "insert" then c.offset(0,5)="xxx-" & c.offset(0,5)
Next c
End with

End Sub

"Solutions Manager" wrote:

I have a worksheet named "sales" with custom ad codes in Column C and sales
rep initials in Column H. I need to write a macro that will look at Column C
and for every size code = "insert" to offset + concatenate. So for example,
If a reps initials were ABC, for every cell in Column C = "insert" the cell
in the same row, column H should be equal to xxx-"ABC".
Is this possible? (currently, i go through a horrendous process involving
Indirect lookups and a copy/paste from a hidden worksheet -very clumsy even
though it works)

If cell in column C = "insert" then OFFSET+CONCATENATE
 

BINGO. BINGO. Thank you. Thank you. Thank you.

"cush" wrote:


Try something like:

sub Test()
DIM c as range
DIM r as long, i as long

With Sheets("Sales")
r=.cells(.rows.count, 3).End(xlUp).row
For each c in .range("C2:C" & r) 'assuming there is a header in C1
If c = "insert" then c.offset(0,5)="xxx-" & c.offset(0,5)
Next c
End with

End Sub

"Solutions Manager" wrote:

I have a worksheet named "sales" with custom ad codes in Column C and sales
rep initials in Column H. I need to write a macro that will look at Column C
and for every size code = "insert" to offset + concatenate. So for example,
If a reps initials were ABC, for every cell in Column C = "insert" the cell
in the same row, column H should be equal to xxx-"ABC".
Is this possible? (currently, i go through a horrendous process involving
Indirect lookups and a copy/paste from a hidden worksheet -very clumsy even
though it works)