Weird range/offset problem

[itchyII]

Hi Everyone,
I am experiencing something very frustrating. I am working in Excel 2000 and I have this really volatile workbook with a couple of input sheets and then lots of calculations and formulas populating ranges on other sheets that produce all kinds of graphs. In one of the spreadsheets, there is a column that contains the column letters of another worksheet (it refers to questions on the other worksheet, and the answers to the questions are in the adjacent column to the right of the question). I have a function that I created that reads this column letter and is supposed to derive the letter of the column next to the one being refered to in the text of the column(basically, I want to determine the answer column letter). Example: text in cell = C, the function should output "D" to indicate the next column over. I know that the simple answer would be to store the letters of the answer column instead of the question column but I can't do this for other reasons. Anyhow, the following is the code that I am using to get the letter that I am looking for.

Function findResponseCol(quest As String) As String

Dim wks As Worksheet
Dim rows As Integer

Dim col As String
Dim x As Integer

Set wks = Worksheets("Questions")

'get number of rows in worksheet
rows = findNumRows(wks, "E", 1, "Total")

'loop through rows on the 'Questions' worksheet
For x = 1 To rows
    'find the matching question
    If wks.Range("E" & x + 1).Value = quest Then
        'goto column that contains the column letter and use offset
        'to get the next column letter
        col = Range(wks.Range("J" & x + 1).Value & "1").Offset(0, 1).Address
        findResponseCol = mid(col, 2, Len(col) - 3)
        Exit For
    End If
Next x
Set wks = Nothing

End Function

The problem is that it doesn't always work! Sometimes it does and sometimes it outputs a column letter completely wrong. For example, wks.Range("J" & x + 1).Value = C and the function is outputting 'E'. For debugging purposes, I even tried

col = Range(wks.Range("C1").Offset(0, 1).Address

and it still output 'E'. This function gave me similar problems yesterday, and after debugging for about 45 minutes, I gave up and closed the workbook. When I re-opened it. It was working fine again. I tried this this time, but its not working. The code seems to be ok. Do any of you think that there could be a memory leak in my code somewhere that's causing these unexpected results?

ItchyII

 

[TonyJollans]

Hi itchyII,

It's not easy to follow without looking at your data and your hardcoded C1 example is invalid as posted. Breaking down your code

col = Range(wks.Range("J" & x + 1).Value & "1").Offset(0, 1).Address

This will give the address of a range in the active worksheet. That range is one column offset from the cell addressed by ..

wks.Range("J" & x + 1).Value & "1"

which (if x = 1) is equal to the letter in cell J2 on the Questions sheet (and row 1) - if cell J2 contains "C", the address will be "C1" - when you offset one column from this and take the mid$ it will give "D".

You do seem to understand (probably better than me) what it is you're working with, but when you assert that wks.Range("J" & x + 1).Value = "C" are you sure that you are looking in the right cell? Sorry to be blunt but I can see no way for you to get the answer you say with the input you say so the only thing I can suggest is that you may be looking in the wrong cell and not working with the data you think you are.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[itchyII]

Hi Tony,
I apologise, for the messed up code, the hard coded version I tried was indeed:

col = Range("C1").Offset(0, 1).Address

I assure you, I am looking at the right cell! I know it sounds crazy, and that's why I've posted! I added in a couple of message boxes to be sure about what I am looking at:

For x = 1 To rows
    'find the matching question
    If wks.Range("E" & x + 1).Value = quest Then
        MsgBox wks.Range("J" & x + 1).Value
        'goto column that contains the column letter and use offset
        'to get the next column letter
        col = Range(wks.Range("J" & x + 1).Value & "1").Offset(0, 1).Address
        MsgBox col
        MsgBox mid(col, 2, Len(col) - 3)
        findResponseCol = mid(col, 2, Len(col) - 3)
        Exit For
    End If
Next x

And this is ouputting three message boxes
C
$E$1
E

in that order!

This is no joke! Something really weird is going on!

ItchyII

 

[PHV]

Just a though:
col = [highlight]wks.[/highlight]Range(wks.Range("J" & x + 1).Value & "1").Offset(0, 1).Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi ItchyII,

You don't have any merged cells, do you? If C1 and D1 are merged, the offset(0,1) will give E1.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[itchyII]

Hi PHV, that seems to do the trick! I dind't think about it because I am not really referncing the range in the 'wks' worksheet, but I guess it doesn't matter!

Thanks!

ItchyII

Thanks Tony, for your help as well!

 

[TonyJollans]

Hi itchyII,

I'm glad you're sorted, but PHV's solution is a workaround because it doesn't matter what sheet you use as all you're doing is using cell addresses as a calculator. If you don't know what's causing it it could happen again. I'm interested: did you check if you had any merged cells?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[itchyII]

Hi Tony,

I know what you mean, but sometimes a when we are time restrained, a work around is just what we need! But, that being said, you made me go back and investigate further.

As for your question, no there are no merged cells in the sheet that I am looking at (the one referenced by wks). However this function is called by another function, that is referncing a different worksheet (in fact, it has refernces to 3 differnt worksheets, all of which have merged cells). What I am thinking is that because I was not explicitly referencing a worksheet with my original statement:

col = Range(wks.Range("J" & x + 1).Value & "1").Offset(0, 1).Address

The system may have used the active worksheet as a reference, and the active sheet did have merged cells!

It just goes to prove, that if you look hard enough, there is an explanation for any problem!!

Thanks Tony, I'll sleep better tonight.....or maybe not.

 

[TonyJollans]

Thanks itchyII,

Yes, the active sheet will be referenced by default from code running in a standard module. Results explained, that's what I like

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.