Problems with Range

**tneva** · 02-02-2009, 06:07 AM

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 1 And Target.Value <> "" Then
       ThisWorkbook.Sheets("gg").Cells(Target.Row, 2).Formula = yadda yadda yadda
   End If
End Sub

There's the sub. Basicly if cell that got changed is in A column and value in the cell isn't empty I want to set formula to B column of that cells row(and use cells value in formula). However it halts in the If clause claiming type mismatches.

Where it is missing? My guess is that value but didn't range objects have value?

(btw how would this operate if more than one cell gets changed. Say copy&paste order?)

**Richard Schollar** · 02-02-2009, 06:20 AM

Syntax looks OK to me. Is it only a single cell you are changing when the code fails? Just to be perfectly clear, please indicate exactly which line the code fails on and provide the exact error the debugger reports.

Richard

**tneva** · 02-02-2009, 06:27 AM

Originally Posted by RichardSchollar

Syntax looks OK to me. Is it only a single cell you are changing when the code fails? Just to be perfectly clear, please indicate exactly which line the code fails on and provide the exact error the debugger reports.

Richard

Yes cell A251 is the one that change. It stops in the line with IF in it(2nd line) with runtime error 13: Type Mismatch.

No more information comes in error message.

And here's the line in full:

If Target.Column = 1 And Target.Value <> "" Then

Edit: Seems to be target.value that is causing problems. Removed it, if clause passed, halts in same error in next line where there is, or atleast should be, yet another target.value(which is just a number like 424635).

**Andy Pope** · 02-02-2009, 06:33 AM

I get a type mismatch error if the Target range is more than 1 cell.

You can not check the Value of multiple cells.

**tneva** · 02-02-2009, 06:47 AM

That's odd since there should be only one cell changing :-( Though does mean this doesn't work anyway since just because now it should be one cell doesn't mean in future there won't be multiple lines added.

Oh bugger.

**Andy Pope** · 02-02-2009, 06:51 AM

So you just need to code for multiple cells.

Should each row in the target range have a formula applied?

What is the proper formula?

**tneva** · 02-02-2009, 07:06 AM

Originally Posted by Andy Pope

So you just need to code for multiple cells.

Should each row in the target range have a formula applied?

What is the proper formula?

Yes for every row where number gets added.

Formula is:

=IF(A303="";"";PIBVSearch(2;"server";"";"";"";"";"*-200 day";"";"";"S.0";256;PIBVUnitBatchSearchMasks(TEXT(A303;0);"";"";"";"")))

Where A303 is cell that got changed. Ultimately I will be wanting to change "*-200 day" to be date which is dependant on row above this which is why I'm going macro way(can't figure way to get it working by adding formula to cell itself. I can put date there but how to get it calculate from date above).

Result will be date and time.

**Andy Pope** · 02-02-2009, 07:10 AM

So something more like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    
    If Target.Column = 1 Then
        For Each rngCell In Target.Cells
            If rngCell.Value <> "" Then
                ThisWorkbook.Sheets("gg").Cells(rngCell.Row, 2).Formula = "=1"
            End If
        Next
    End If
    
End Sub

Change the formula to your formula with user defined functions.