Replace single recurring value with value from list

**nashi** · 11-28-2013, 01:45 AM

Hello! I'm a major coding newbie and recently had someone make a request that I can't fulfill. I know it's possible, but I don't have the knowhow to put it into action, so I was wondering if anyone here could help.

Basically we're working with a huge spreadsheet, 8 columns, 2000 rows, and there's a particular value (we'll say it's the word "NAME") that occurs multiple times in each row. We want that value to be replaced row by row with a different value from a list. So, for instance, on row 1 "NAME" would be replaced with "NAME_LOWERCASE", and then on row 2 with "NAME_UPPERCASE", and row 3 with "NAME_BACKWARD," etc. There are a total of 6 different values we need to replace the original 1, row by row through the entire sheet. Each instance of the original value in the row needs to be replaced with the same variable value from the list, through the entire sheet.

It's way outside my knowledge of code, but it feels like it should be simple. (Kind of driving me insane, like having a word on the tip of my tongue.) Any help would be appreciated!

**millz** · 11-28-2013, 02:31 AM

To have a clearer picture, please post a sample of the workbook, maybe with about 50 rows, to see how is the "NAME" being repeated. Also provide the list of values to be replaced with. And on a separate worksheet with the desired results shown would be very helpful.

**HaHoBe** · 11-28-2013, 02:53 AM

Hi, nashi,

the items are on a sheet called List starting in A1 and B1 going down the rows, the data is located in sheet Data (alter names and ranges to suit):

Sub EF971666()
Dim wsList As Worksheet
Dim wsData As Worksheet
Dim lngCount As Long

Set wsList = Sheets("List")
Set wsData = Sheets("Data")

For lngCount = 1 To wsList.Range("A" & Rows.count).End(xlUp).Row
  wsData.UsedRange.Replace _
      What:=wsList.Cells(lngCount, "A"), _
      Replacement:=wsList.Cells(lngCount, "B"), _
      LookAt:=xlWhole, _
      SearchOrder:=xlByRows, _
      MatchCase:=False, _
      SearchFormat:=False, _
      ReplaceFormat:=False
Next lngCount

Set wsData = Nothing
Set wsList = Nothing
End Sub

Ciao,
Holger

**millz** · 11-28-2013, 03:21 AM

Hi Holger,

I believe what OP wants is very different. I am guessing he has "NAME" across several rows, and then repeats in a certain sequence.

e.g.

1 "NAME"    --to be replaced with-->    "NAME_LOWERCASE"
2 "NAME"    --to be replaced with-->    "NAME_UPPERCASE"
3 "NAME"    --to be replaced with-->    "NAME_BACKWARDS"
4 ...
5 ...
6 ...
7 "NAME"    --to be replaced with-->    "NAME_LOWERCASE"
8 "NAME"    --to be replaced with-->    "NAME_UPPERCASE"
9 "NAME"    --to be replaced with-->    "NAME_BACKWARDS"

That's my understanding though, could be wrong. We'll see what OP says.

**HaHoBe** · 11-28-2013, 03:28 AM

Hi, miliz,

this sentence made up my code:

Originally Posted by Opening post

Each instance of the original value in the row needs to be replaced with the same variable value from the list, through the entire sheet.

Ciao,
Holger

Replace single recurring value with value from list

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Replace single recurring value with value from list

Re: Replace single recurring value with value from list

Re: Replace single recurring value with value from list

Re: Replace single recurring value with value from list

Re: Replace single recurring value with value from list

Thread Information

Users Browsing this Thread

Similar Threads

Identifying most recurring word or phrase from a list of remarks in excel

Multiple Find and Replace to replace a list of strings

Macro required to generate recurring list using frequency and calendar inputs

Transposing Recurring List of Records

replace number from a range with single value

Bookmarks

Bookmarks

Posting Permissions