VBA array translation

[Charmwah]

Hi all

I'm wondering if anybody can simply translate a line of VBA code for me. It was written by somebody else, and i'm struggling to make sense of it. I'd attach the whole file but privacy laws forbid it i'm afraid. The line is:

Set Answer = Sheets("Sheet1").Range(Replace(Cells(2, Question.Column).Address(False, False), "1", "") & ":" & Replace(Cells(2, Question.Column + 5).Address(False, False), "1", "")).Find(Target.Offset(0, -4).Value)

On debugging this code I find that "Answer = Nothing", when it should be pointing to a specific column value.

To give further information, this line of code is taken from a macro that copies a value from Sheet2 into Sheet1, but uses several variables to work out where in Sheet2 the value is copied.

I understand its an obscure request, but I'd appreciate any pointers all the same.

Thanks in advance!

 

[Fluff]

At a guess try changing the Cells(2, to Cells(,1 on both occasions.

 

[Charmwah]

Hi Fluff

I've actually just done that very thing and it no longer has a bug and will copy the data. However, its now copying the data to the second column in Sheet 1 (where column 1 is the list of customers). So effectively the code isn't identifying the correct column with which to place the value.

Any ideas?

 

[Fluff]

That code is simply finding a value on sheet 1, nothing else.

 

[Charmwah]

Yes sorry, this line of code is determining the cell to copy the value to. But its presumably not determining the correct cell, because like I say when the 'paste' happens its in the wrong location. Perhaps the full code for the process would help:

If MsgBox("Are you sure you want to change the variable in cell " & Cells(Target.Row, 4).Address(0, 0) & "?", vbYesNo) = vbYes Then
                Set Customer = Sheets("Sheet1").Range("A:A").Find(Range("D4"), LookIn:=xlValues, LookAt:=xlWhole)
                Set Question = Sheets("Sheet1").Rows(2).Find(Range("D8"), LookIn:=xlValues, LookAt:=xlWhole)
                Set Answer = Sheets("Sheet1").Range(Replace(Cells(1, Question.Column).Address(False, False), "1", "") & ":" & Replace(Cells(1, Question.Column + 4).Address(False, False), "1", "")).Find(Target.Offset(0, -4).Value)
                Sheets("Sheet1").Cells(Customer.Row, Answer.Column) = Target
                Target.Offset(0, -1) = Target
            End If

Thanks Fluff.

 

[Fluff]

Without knowing what your data is like, or what values are being searched for, its very difficult to tell.
Especially as you have a message box asking if you want to change the values in a specific cell, but then the code is running various Finds to determine what cell to change.

 

[Charmwah]

Hi Fluff

I appreciate its almost working blind, but i'm afraid privacy laws forbid me from posting the file. If it helps any, Sheet1 is essentially a table arranged as a database, with Column A as the customers, then Row 1 Is the 'Family', Row 2 is the 'Question', and Row 3 is the 'Answer'. These first 3 rows are all headings, so the data doesn't actuall begin until the first customer row, row 4. There are 6 different answers and they're exactly the same for each Question (all questions are different, as are all families). I think it is this aspect that is causing the issue, since the code is finding the first matching 'answer' and copying the value accordingly, rather than finding the question and then the answer. The only cell values are 'yes' or 'no'.

Make any sense?

 

[Fluff]

Make any sense?

Fraid not.
Unless you can supply some de-sensitised data, I'm not sure there is much I can do to help.