Offset based on Values in Column E

**mrmmickle1** · 12-04-2012, 01:41 AM

I was able to resolve this issue by changing the order of search keys 1 and 2 and now the code seems to work fine.

**tfurnivall** · 12-04-2012, 04:27 AM

Hi mrmmickle!

Your problem (originally) seemed to be:

How can I insert a value into a cell, based on the contents of another cell in the same row. Given that you dramatically expanded the number of phrases to check for, and the number of replacements, would it not be easier to divide the task into 2 separate pieces?

1. Build the list of phrases to check for, and the associated replacement phrase, and

2. Search through the column, and and see how many 'replacements' you can make.

Task #1 is easily defined as an array - of two dimensions

Code:

Const MaxElements = 100
Const NumColumns = 2

Dim ReplacementChart(MaxElements, NumColumns) As String

(By using the symbolic constants you need only change one place whenever you refer to the ReplacementChart)
Loading this array is left "as an exercise for the reader", but my guess is that you may want to have a separate sheet where it resides, and allow the array simply to read data from that sheet. Either way, not a really big deal.

Task #2 involves a little bit more effort:

Code:

Const ColumnToSearch = "A"    '   or whatever column you want to use to find those phrases
Const ColumnToReplace = "E"      '   or whatever you decide - this is 4 columns over, as you requested)
Const MatchPhrase = 1
Const ReplacePhrase = 2
Const FirstElement = 0             '   from the system definition of the first row in an array. Could also be 1
Const MaxElements = 100      '   From how ever you set up the ReplacementChart array in task 1

Dim FirstRowToCheck As Integer
Dim LastRowToCheck As Integer
Dim sheet As Worksheet      '   You do need to set this to the sheet where you want to work, whatever it's called.....

Dim r As Integer        '   Row we are looking at
Dim l As Integer        '   line in the Replacement Chart
For r = FirstRowToCheck To LastRowToCheck
    l = FirstElement
    While l < MaxElements
           If ReplacementChart(l, MatchPhrase) = sheet.Range(ColumnToSearch + Format(r)) Then
              l = MaxElements       '   Force the While loop to terminate
              sheet.Range(ColumnToReplace + Format(r)) = ReplacementChart(l, ReplacementPhrase)
           End If
           l=l+1
    Wend

Purists will argue that this is an inefficient algorithm, because it forces you to do a sequential scan of a range, and then check every entry in the Replacement Chart.
I'd say that you only search the ReplacementChart until you find a match, and no cell can possibly have more than one match. I think that the clarity of the algorithm will make it easier when you refine your requirements (as you have done already ;-)

HTH - if not now, perhaps in the future!

Tony

**mrmmickle1** · 12-04-2012, 10:06 AM

Tony,

Thank you for the time and effort you have put into this. It makes a lot of sense, however some elements are a little confusing to me, simply because this is the first time I am seeing them. I will have to review this further and look up a few of the terms and uses. It looks very thorough though. I am sure I can figure it out. This helps a lot. Thanks!!