CODEWITHSUNDEEP
vbaexcel
WouldBeNerd
WouldBeNerd

Reputation: 687

unable to set workbook variable to ActiveWorkbook

This is blowing my mind. I can't find what I'm doing wrong. I hope it's just a case of tunnel vision. I get error message "Object variable or With block variable not set- 1" 

Option Explicit:
Public mWB As Workbook
Public Sub runCSSBatch()
  On Error GoTo Errorcatch

1    mWB = ActiveWorkbook
     Call createTempSheet
     Call findworksheet

  Errorcatch:
     MsgBox Err.Description & "-" & Erl
     Application.DisplayAlerts = False
     mWB.Sheets("TEMP").Delete
     Application.DisplayAlerts = True

End Sub

Upvotes: 0

Views: 1234

Answers (2)

WouldBeNerd
WouldBeNerd

Reputation: 687

I eventually found many things wrong with my script.

  • I did end up using Set in front of ActiveWorkbook (using ThisWorkbook was not necessary)
  • I believe the comment about using 1: instead of 1 to catch the error was valid.
  • I am now running the script with quite a few less subs than I was before.
  • I also had made the mistake of using Cells() inside Range() when one excludes the other
  • I tried to pass a Worksheet Variable to a Sub (apparently you can't do that).
  • I'm sure there was more but I can't recall.

I'm going to chalk it up to having a shitty day. :/ As you can see the below code looks nothing like what I had posted initially.

Option Explicit:
Public mWB As Workbook
Public Sub runCSSBatch()
   Set mWB = ActiveWorkbook
    mWB.Sheets.Add.Name = "TEMP"
    Dim WSh As Worksheet
    For Each WSh In mWB.Worksheets
        If InStr(WSh.Name, "CSS") = 1 Then
            Call parseRowText(WSh.Name)
        End If
    Next
End Sub
Private Sub parseRowText(WSName As String)
Dim rowCount As Long
Dim I As Long
Dim columnCount As Long
Dim B As Long
Dim dataString As String
Dim WS As Worksheet
Set WS = mWB.Worksheets(WSName)

columnCount = mWB.Sheets(WSName).UsedRange.Columns.Count
rowCount = mWB.Sheets(WSName).UsedRange.Rows.Count

For I = 2 To rowCount
    For B = 1 To columnCount
        dataString = ""
        If mWB.Sheets(WSName).Cells(1, B).Value = "STOP" Then
            dataString = "}"
            Call addToTempSheet(dataString)
            Exit For
        Else
         If B = 1 Then
             dataString = mWB.Sheets(WSName).Cells(I, B).Value & "{"
             Call addToTempSheet(dataString)
         Else
            If dataString & mWB.Sheets(WSName).Cells(I, B).Value = "" Then
            Else
                dataString = mWB.Sheets(WSName).Cells(1, B).Value & ":"
                dataString = dataString & mWB.Sheets(WSName).Cells(I, B).Value & ";"
                Call addToTempSheet(dataString)
            End If
         End If
        End If
    Next B
Next I
End Sub
Private Sub addToTempSheet(dString As String)
    mWB.Sheets("TEMP").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = dString
End Sub

Upvotes: 0

Davesexcel
Davesexcel

Reputation: 6984

Instead of ActiveWorkbook, it may be, ThisWorkbook

set mwb=thisworkbook

Upvotes: 1

Related Questions