VBA Excel- Application or Object-Defined Error

Question

Phase 1

I am attempting to search through the first column of every row in a range that has a value (formula, text, numbers, etc.) for a string. This string is generated by selecting from a drop down list. The selections are formatted like "Desktop, Dell, 790 - 4GB" and I am only concerned with the string of text before the 1st comma (AKA "Desktop" in this example.) I am using a Split() method in order to get the word before the first comma, and then attempting to use a Case statement to insert a string into another cell in the same row.

Phase 2

I am using what is selected in the first drop down list to populate a second drop down list with pre-determined values.

The Problem

The program is throwing Run-time error '1004': Application_defined or object-defined error. I don't know where to start.

Original Code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Call splitter
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Not Target.HasFormula Then Target.Value = UCase(Target.Value)
End If
Whoops:
Application.EnableEvents = True
End Sub

Sub splitter()
   Dim line() As String
   Dim rng, row As Range
   Dim lRow, lCol As Long
   lRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).row
   lCol = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
   Set rng = Cells(lRow, lCol)

   For Each row In rng.Rows
    If row.Value <> "" Then
        line = Split(Range(row, 1), ",")
        Select Case line(0)
           Case "Desktop"
               Range(row, 8).Value = "Desktop"
           Case "Laptop"
               Range(row, 8).Value = "Laptop"
           Case "Server"
               Range(row, 8).Value = "Server"
           Case Else
               Range(row, 8).Value = "N/A"
        End Select
    End If
   Next
End Sub

Revised Code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Call splitter
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Not Target.HasFormula Then Target.Value = UCase(Target.Value)
End If
Whoops:
Application.EnableEvents = True
End Sub

Sub splitter()
   Dim line() As String
   Dim rng As Range, row As Range
   Dim lRow As Long
   lRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).row
   Set rng = Cells("A1:N" & lRow)

   For Each row In rng.Rows
    If row.Value <> "" Then
        line = Split(Cells(row, 1), ",")
        Select Case line(0)
           Case "Desktop"
               Cells(row, 8).Value = "Desktop"
           Case "Laptop"
               Cells(row, 8).Value = "Laptop"
           Case "Server"
               Cells(row, 8).Value = "Server"
           Case Else
               Cells(row, 8).Value = "N/A"
        End Select
    End If
   Next
End Sub

Three things 1) Dim rng, row As Range Change it to Dim rng As Range, row As Range else rng will be declared as a variant. Change for others as well 2) Range(row, 8).Value should be cells(row, 8).Value change for others as well 3) Also regarding For Each row In rng.Rows, rng is just 1 cell it will not have rows. What exactly are you trying to do? — Siddharth Rout
– Siddharth Rout, Commented Jul 12, 2012 at 21:55
I think I understand what you are trying to do... One moment... — Siddharth Rout
– Siddharth Rout, Commented Jul 12, 2012 at 22:03
Alright, I revised the code. See OP. now my error is: Invalid Procedure Call or Argument — Josh McKearin
– Josh McKearin, Commented Jul 12, 2012 at 22:06
Are all the dropdowns in Col B or Col A?... I am almost done with the changes to your code.... — Siddharth Rout
– Siddharth Rout, Commented Jul 12, 2012 at 22:06
1st dropdown is in Col A, 2nd dropdown will be in Col H (phase 2) — Josh McKearin
– Josh McKearin, Commented Jul 12, 2012 at 22:09

Siddharth Rout · Accepted Answer · 2012-07-13 00:10:32Z

2

I have combined your two codes into 1.

PHASE 1

Is this what you are trying?

Private Sub Worksheet_Change(ByVal Target As Range)        
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        If Target.Cells.Count > 1 Then GoTo LetsContinue

        If Target.Value <> "" And InStr(1, Target.Value, ",") Then
            Select Case Split(Target.Value, ",")(0)
               Case "Desktop": Range("H" & Target.row).Value = "Desktop"
               Case "Laptop":  Range("H" & Target.row).Value = "Laptop"
               Case "Server":  Range("H" & Target.row).Value = "Server"
               Case Else:      Range("H" & Target.row).Value = "N/A"
            End Select
        End If
    ElseIf Not Intersect(Target, Columns(2)) Is Nothing Then
        If Target.Cells.Count > 1 Then GoTo LetsContinue
        If Not Target.HasFormula Then Target.Value = UCase(Target.Value)
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

edited Jul 13, 2012 at 0:10

answered Jul 12, 2012 at 22:16

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Josh McKearin Over a year ago

OK! This fixes my issue perfectly! There is only one issue. The code in the Worksheet_Change subroutine was for Uppercasing all text in Col B. With your code here, I have lost that functionality.

Josh McKearin Over a year ago

Beautifully done! Thank you so much! Now time to work on Phase 2.

Siddharth Rout Over a year ago

For Phase two is this what you are trying? siddharthrout.wordpress.com/2011/07/29/…

Josh McKearin Over a year ago

That is exactly what I need to do and will follow your tutorial and see if I can figure it out (for the sake of learning the material.) If I run into any problems, I will comment here.

Josh McKearin Over a year ago

let us continue this discussion in chat

|

Collectives™ on Stack Overflow

VBA Excel- Application or Object-Defined Error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related