Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Type mismatch help

    Hi guys,

    When I delete the content of the relevant cell, making it "" (empty), I get "Run time error '13': Type mismatch" on the bolded line of the vba...

    A.) Can someone enlighten me why this might be?

    Code:
        If zRow = 4 Then                                                        'if all items sold make Seller name black..
        '.......................................................................
            Target.Offset(-2, -1).Font.Color = RGB(255, 255, 255)
            If Target + Target.Offset(1) + Target.Offset(2) + Target.Offset(3) + Target.Offset(4) = 0 Then
                Target.Offset(-2, -1).Font.Color = RGB(0, 0, 0)
            End If
        End If
    B.) Is there a way to describe this range in a shorter fashion? (works as it is, just interested in learning )

    Code:
    If Target + Target.Offset(1) + Target.Offset(2) + Target.Offset(-1) + Target.Offset(-2) = 0 Then

    Thank you
    Ferenc
    Last edited by Ferenc Nagy; 2015-07-14 at 07:58.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    You could try:

    Code:
    Dim rng As Range
    With Target
        Set rng = Range(.Offset(-2), .Offset(2))
        If WorksheetFunction.CountA(rng) = 0 Then
            MsgBox "Hello"
        End If
    End With
    HTH,
    Maud

  3. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Lightbulb

    Thank you Maudibe,

    Quote Originally Posted by Maudibe View Post
    You could try:

    Code:
    Dim rng As Range
    With Target
        Set rng = Range(.Offset(-2), .Offset(2))
        If WorksheetFunction.CountA(rng) = 0 Then
            MsgBox "Hello"
        End If
    End With
    HTH,
    Maud
    This seems to work, save a few characters and make it a lot more uniformed for each row...

    If WorksheetFunction.Sum(Range(Target.Offset(-2), Target.Offset(2))) = 0 Then

    How would I make sure that this condition only goes through if the SUM is 0, but not Blank with the least amount of code?

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Farenc,

    Your formula will work only with numbers
    If WorksheetFunction.Sum(Range(Target.Offset(-2), Target.Offset(2))) = 0 Then

    My formula will work with both numbers and text
    WorksheetFunction.CountA(Range(Target.Offset(-2), Target.Offset(2))) = 0 Then

    You can simplify my formula the way I coded it above.

    Your choice based on the type of data you are using.

    HTH,
    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •