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

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" wrote in message
...

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

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
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=500720

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

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" wrote in message
...

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

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

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" wrote in message
...

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

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


--
Tomski
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=500720