How to update the value in an OFFSET cell

[ififthelement]

Hi,

I have Sheet1 which has following data:
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]a, b, c, d[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]=FindCount(A1)[/TD]
[/TR]
</tbody>[/TABLE]





In the VBA-Project, I have a Module with following code :

Public Function FindItemCount(LinkedItems As Range)
On Error Resume Next
Dim tmpItems, curAddr
Dim indx As Integer
Dim LocOffset

tmpItems = Split(LinkedItems.Value, ", ") ' Delimiter is COMMA & SPACE
Set LocOffset = LinkedItems.Offset(0, 1) ' to set a value in B1

indx = UBound(tmpItems)

LocOffset.Value = indx ' Generates Application-defined or Object-defined Error - Why ?
' Do other necessary stuff


End Function


The intent is only to find how many items are there and update the OffSet COLUMN ( not happening) with a value of the count.
Then read read each item and do necessary work.

 

[oldbrewer]

why not just count the commas and add 1

 

[MickG]

Perhaps this:-

Function Fcount(R As Range) As Integer
Fcount = IIf(UBound(Split(R, ",")) = 0, 1, UBound(Split(R, ",")))
End Function

 

[ififthelement]

clever answer, but the issue is in the line LocOffset.Value = indx ' Generates Application-defined or Object-defined Error - Why ?
However, If i replace the code above with FindItemCount= indx, which works well and puts the indx in the cell C1 - where the function was called. But I want to use the offset and put the value in another cell.

If OffSet only to READ value. Can it not update the Cell it refers to ?

 

[MickG]

From my understanding , it can't be done!!!!

 

[oldbrewer]

iteration loop if you look at a cell from one address and try to update the looked at cell

it is like saying =if(A1=4, A1=5, B1=6)

 

[ififthelement]

Hi,

I may not have the adjacent column available always, so I wanted to use OffSet to specify where i want the update to happen.

1) Is OffSet only to READ value and not update ?
2) Does OffSet work only when I do .SELECT & using ActiveCell ? because there are a couple of example where they typically do .SELECT & use ActiveCell

Can someone pick this query ? I still dont have a reply to the actual query

 

[oldbrewer]

=offset($A$1,4,5) will return the value in E4 to A1

E4 will not be changed

instead of 4 and 5 you could build 2 match functions into the offset formula

 

[oldbrewer]

if you want to update any cell dependent on certain criteria,you need to use vba

for example if your data is 4,5,6 and that means you want to put 6 into cell E4

cells(4,5)=6

if A1 = 4 and B1 =5 and C1=6

cells(cells(1,1),cells(1,2))=cells(1,3)