CODEWITHSUNDEEP
vbaexcel
Max Segal
Max Segal

Reputation: 2045

Range object returns empty values

I have a set range to a variable in this fashion:

Dim srcRng As Range
Set srcRng = Range(hrwb.Worksheets(1).Range(yomColAddress)(1).Address, _
Cells(hrwb.Worksheets(1).Range(yomColAddress).row + 200, rightMostCol)(1).Address)

for some weird reason when I call

srcRng(1) 'actually instead of 1 is i that runs 1 to srcRng.Count

it doesn't return the upper leftmost cell value. Any ideas why?

(for those who are not familiar with this technique: http://www.cpearson.com/excel/cells.htm)

Informations:

at execution time the variables yomColAddress=$AL$9 and righMostCol=40

hrwb.Worksheets(1).Range(yomColAddress)(1) works as expected.

With MsgBox srcRng(1).Address & " value:" & srcRng(1).Value I get "$AL$9 value:"

The value of AL9 is the text "yom"

The actual code is:

Dim srcRng As Range
Set srcRng = Range(hrwb.Worksheets(1).Range(yomColAddress)(1).Address, Cells(hrwb.Worksheets(1).Range(yomColAddress).row + 200, rightMostCol)(1).Address)
Dim i As Integer
i = 1
While (weekDayCol = 0 And i <= srcRng.count)
    If loneHebDayLetter("à", "ä", srcRng(i)) Then'loneHebDayLetter checks some conditions on a cell
        weekDayCol = srcRng(i).Column
    End If
    i = i + 1
Wend

Upvotes: 1

Views: 2230

Answers (1)

smagnan
smagnan

Reputation: 1257

I think I get what goes wrong here:

The code itself is working well but not on the good data (This is a supposition but I just did some tests with a custom workbook)

Short version

Just add srcRng.Select after Set srcRng (no real interest but to understand what it does) and I think you will get what happens if my supposition is correct.

Longer version

When you do Set srcRng = ... it does create the correct Range but it is not linked to any sheet actually ... It just means remember a Range which goes from cell X to cell Y.

The point is: The sheet (let's say "sheet2") where your code is executed isn't the same as the one where the datas are (say "sheet1") so srcRng(1) is understood as Sheets("sheet2").srcRng(1) instead of Sheets("sheet1").srcRng(1) (<- that's what you want)

Even if not elegant, this should work:

Dim srcRng As Range
With hrwb.Worksheets(1)
    Set srcRng = Range(.Range(yomColAddress)(1).Address, Cells(.Range(yomColAddress).row + 200, rightMostCol)(1).Address)
    Dim i As Integer
    i = 1
    While (weekDayCol = 0 And i <= srcRng.count)
        If loneHebDayLetter("à", "ä", .Range(srcRng.Address)(i).Value) Then 'I assume it take the value not the cell: if it take the cell you may get an error!
            weekDayCol = srcRng(i).Column
        End If
        i = i + 1
    Wend
End With

What is important is the use of .Range(srcRng.Address)(i).Value to access the value in the right worksheet! (That's why this trick is not needed here: srcRng(i).Column because colum numbers do not change from one sheet to an other)

(NOTE: I used with to optimize/clarify the code)

If something isn't clear tell me

Upvotes: 1

Related Questions