Pointing directly to a cell in a range

[Tomski]

Hi guys,

Just a quick one, is it possible to point directly to the contents of a cell that is in a range, although we don't know where, without using a loop to check each cell. For example (note sure which is more correct, but you get the idea)

MyCell.Offset(0, 3) = wkBk.Worksheets("All Data Unnarr").Range("B1:B500").Cells("C-ACCOUNTS Total").Offset(0, 3)

or

MyCell.Offset(0, 3) = wkBk.Worksheets("All Data Unnarr").Range("B1:B500").Range("C-ACCOUNTS Total").Offset(0, 3)

Thanks,

Tom

[Bernie Deitrick]

Tom,

Yes, though you need to better explain your criteria for selecting a cell.

Perhaps?

MyCell.Offset(0, 3).Value = _
wkBk.Worksheets("All Data Unnarr").Range("B1:B500").Cells(Range("C-ACCOUNTS
Total").Value).Offset(0, 3).Value

where the value in a cell named "C-ACCOUNTS Total":

Range("C-ACCOUNTS Total").Value

is the indexing value that you want to use

HTH,
Bernie
MS Excel MVP


"Tomski" <[email protected]> wrote in message
news:[email protected]...
>
> Hi guys,
>
> Just a quick one, is it possible to point directly to the contents of a
> cell that is in a range, although we done know where, without using a
> loop to check each cell. For example (note sure which is more correct,
> but you get the idea)
>
> MyCell.Offset(0, 3) = wkBk.Worksheets("All Data
> Unnarr").Range("B1:B500").Cells("C-ACCOUNTS Total").Offset(0, 3)
>
> or
>
> MyCell.Offset(0, 3) = wkBk.Worksheets("All Data
> Unnarr").Range("B1:B500").Range("C-ACCOUNTS Total").Offset(0, 3)
>
> Thanks,
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=500720
>

[Tomski]

Only just had a chance to start looking at todays excel.

Thanks for that, I will give it a try, but that looks to be just what I was after.

Cheers,

Tom

[Tomski]

I have tried the above and also a line where I took out the 'value' from Cells(Range("C-ACCOUNTS
Total").Value), as I was think that this is like an associate array hence "C-ACCOUNTS Total" would only be needed to reference.

Both throw an error. Any more suggestions as this would save a lot of time as I am using a loop at the mo, I think unnecessarily.


Cheers,

Tom

[Bernie Deitrick]

Tom,

What do you get when you run this:

Sub Test()
Msgbox Range("C-ACCOUNTS Total").Value
End Sub

HTH,
Bernie
MS Excel MVP


"Tomski" <[email protected]> wrote in message
news:[email protected]...
>
> I have tried the above and also a line where I took out the 'value' from
> Cells(Range("C-ACCOUNTS
> Total").Value), as I was think that this is like an associate array
> hence "C-ACCOUNTS Total" would only be needed to reference.
>
> Both throw an error. Any more suggestions as this would save a lot of
> time as I am using a loop at the mo, I think unnecessarily.
>
>
> Cheers,
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=500720
>

[Tomski]

Sorry its taken so long to reply, excel is one of many things I do at work, so it takes me a while to address things.

That doesn't work either, but I am tring to access an inactive workbook. Here is the basis of my code which should give you a better idea.

Its a sub routine that takes copies data from a work book on to a work sheet.


Sub teamData(wkBk As Workbook, wkSt As Worksheet, team As String, datee As Date)

wkSt.Activate

For Each MyCell In Range("A1:A400").Cells
If MyCell = datee Then

MyCell.Offset(0, 3).Value = wkBk.Worksheets("All Data Unnarr").Range("A4:O4").Cells(Range("C-ACCOUNTS").Value).Offset(0, 3).Value



End If
Next MyCell

End Sub


Cheers,

Tom

[Bernie Deitrick]

Tom,

Which workbook is the range "C-ACCOUNTS" in?

What is the value in "C-ACCOUNTS"? Is it numeric, or is it a string?

What is your criteria for selecting a cell from the range Range("A4:O4")? Matching the value of
"C-ACCOUNTS"?

HTH,
Bernie
MS Excel MVP


"Tomski" <[email protected]> wrote in message
news:[email protected]...
>
> Sorry its taken so long to reply, excel is one of many things I do at
> work, so it takes me a while to address things.
>
> That doesn't work either, but I am tring to access an inactive
> workbook. Here is the basis of my code which should give you a better
> idea.
>
> Its a sub routine that takes copies data from a work book on to a work
> sheet.
>
>
> Sub teamData(wkBk As Workbook, wkSt As Worksheet, team As String, datee
> As Date)
>
> wkSt.Activate
>
> For Each MyCell In Range("A1:A400").Cells
> If MyCell = datee Then
>
> MyCell.Offset(0, 3).Value = wkBk.Worksheets("All Data
> Unnarr").Range("A4:O4").Cells(Range("C-ACCOUNTS").Value).Offset(0,
> 3).Value
>
>
>
> End If
> Next MyCell
>
> End Sub
>
>
> Cheers,
>
> Tom
>
>
> --
> Tomski
> ------------------------------------------------------------------------
> Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
> View this thread: http://www.excelforum.com/showthread...hreadid=500720
>

[Tomski]

C-Accounts is the value contained in the cell. I thought that it might be possible to treat a range as a associate array and use the cell values to access certain cells in the range.

The reason I want to select the cell containing 'C-ACCOUNTS' is because I want to get a value in a cell that is 15 rows below.

The range is in workbook wkBk, and the Worksheets is "All Data Unnarr".

i'm not even sure this is possible, I can always use a loop, but I thought this way would be tidier.

Cheers,

Tom