Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If part of a cell = "NVA" then insert "Norfolk" in return cell | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Insert 19 cells "Shift to the right" if cell contains "-" | Excel Programming | |||
Disabling "Entire &row/&column" in Delete/Insert Cell | Excel Programming |