+ Reply to Thread
Results 1 to 3 of 3

Type Mismatch Problem

  1. 05-20-2005, 11:06 AM #1
    Damien McBain
    Guest

    Type Mismatch Problem

    What's wrong with this (I get t type mismatch error on the line with
    "Range(Cells(c, c + 1)) = LevBR")?

    Sub CreateSalesFile()
    Application.ScreenUpdating = False
    Dim DaDate As String
    Dim DaBookName
    Dim LevBR, LevPSE, LevPSW, LevWT, LevPW, VolHMC, VolPW, VolSR, VolFC,
    VOlWFC, VolWT, VolCR, VolWFT, VolDW, VolNMO, VolNMN
    LevBR = Range("bullring")
    LevPSE = Range("PugEast")
    LevPSW = Range("PugWest")
    LevWT = Range("White")
    LevPW = Range("PigmentShed")
    VolHMC = Sheets("Operations").Range("B10")
    VolPW = Sheets("Operations").Range("C10")
    VolSR = Sheets("Operations").Range("D10")
    VolFC = Sheets("Operations").Range("E10")
    VOlWFC = Sheets("Operations").Range("F10")
    VolWT = Sheets("Operations").Range("G10")
    VolCR = Sheets("Operations").Range("H10")
    VolWFT = Sheets("Operations").Range("I10")
    VolDW = Sheets("Operations").Range("J10")
    VolNMO = Sheets("Operations").Range("K10")
    VolNMN = Sheets("Operations").Range("L10")
    DaDate = Range("Date")
    DaBookName = Left(DaDate, 7)

    Sheets("Sales").Select
    Sheets("Sales").Copy
    Workbooks(Workbooks.Count).Activate
    ActiveSheet.Unprotect

    Range("A1").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues

    Range("B8:F17").Copy
    Range("B8:F17").PasteSpecial Paste:=xlPasteValues

    Range("H8:I17").Copy
    Range("H8:I17").PasteSpecial Paste:=xlPasteValues

    Range("B19:F20").Copy
    Range("B19:F20").PasteSpecial Paste:=xlPasteValues

    Range("H19:I20").Copy
    Range("H19:I20").PasteSpecial Paste:=xlPasteValues

    Range("A1").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    Range("A1").Select

    ChDir "I:\Accounting\Daily Tonnes\DailyReports"
    ActiveWorkbook.SaveAs Filename:= _
    "I:\Accounting\Daily Tonnes\DailyReports\" & Range("Date") &
    "-Sales.xls"

    ActiveWindow.Close

    'MsgBox "File: I:\Accouting\Daily Tonnes\DailyReports\" & Range("Date") &
    "-Sales.xls has been created", , "Daily Tonnes"

    'If Not CBool(Len(Dir("I:\Accounting\Daily Tonnes\MonthlyReports\" &
    Left(Range("Date"), 7) & ".xls"))) Then
    'End If

    ChDir "I:\Accounting\Daily Tonnes\MonthlyReports"
    Workbooks.Open "I:\Accounting\Daily Tonnes\MonthlyReports\" & DaBookName
    & ".xls"
    Workbooks(DaBookName & ".xls").Activate
    Sheets("Sheet1").Activate

    For Each c In Range("A5:A35")
    If c.Text = DaDate Then
    Range(Cells(c, c + 1)) = LevBR
    Range(Cells(c, c + 2)) = LevPSE
    Range(Cells(c, c + 3)) = LevPSW
    Range(Cells(c, c + 4)) = LevWT
    Range(Cells(c, c + 5)) = LevPW
    Range(Cells(c, c + 6)) = VolHMC
    Range(Cells(c, c + 7)) = VolPW
    Range(Cells(c, c + 8)) = VolSR
    Range(Cells(c, c + 9)) = VolFC
    Range(Cells(c, c + 10)) = VOlWFC
    Range(Cells(c, c + 11)) = VolWT
    Range(Cells(c, c + 12)) = VolCR
    Range(Cells(c, c + 13)) = VolWFT
    Range(Cells(c, c + 14)) = VolDW
    Range(Cells(c, c + 15)) = VolNMO
    Range(Cells(c, c + 16)) = VolNMN
    Else
    End If
    Next c

    Workbooks(DaBookName & ".xls").Close

    Workbooks("Daily Tonnes Model").Activate
    Sheets("Main").Activate
    Range("Date").Select
    Application.ScreenUpdating = True

    End Sub





    Reply With QuoteRegister To Reply
  2. 05-20-2005, 11:06 AM #2
    Bob Phillips
    Guest

    Re: Type Mismatch Problem

    c is a range object, not a column number or a row number.

    Try

    c.Offset(0,1).Value = LevBR

    etc.


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Damien McBain" <[email protected]> wrote in message
    news:[email protected]...
    > What's wrong with this (I get t type mismatch error on the line with
    > "Range(Cells(c, c + 1)) = LevBR")?
    >
    > Sub CreateSalesFile()
    > Application.ScreenUpdating = False
    > Dim DaDate As String
    > Dim DaBookName
    > Dim LevBR, LevPSE, LevPSW, LevWT, LevPW, VolHMC, VolPW, VolSR, VolFC,
    > VOlWFC, VolWT, VolCR, VolWFT, VolDW, VolNMO, VolNMN
    > LevBR = Range("bullring")
    > LevPSE = Range("PugEast")
    > LevPSW = Range("PugWest")
    > LevWT = Range("White")
    > LevPW = Range("PigmentShed")
    > VolHMC = Sheets("Operations").Range("B10")
    > VolPW = Sheets("Operations").Range("C10")
    > VolSR = Sheets("Operations").Range("D10")
    > VolFC = Sheets("Operations").Range("E10")
    > VOlWFC = Sheets("Operations").Range("F10")
    > VolWT = Sheets("Operations").Range("G10")
    > VolCR = Sheets("Operations").Range("H10")
    > VolWFT = Sheets("Operations").Range("I10")
    > VolDW = Sheets("Operations").Range("J10")
    > VolNMO = Sheets("Operations").Range("K10")
    > VolNMN = Sheets("Operations").Range("L10")
    > DaDate = Range("Date")
    > DaBookName = Left(DaDate, 7)
    >
    > Sheets("Sales").Select
    > Sheets("Sales").Copy
    > Workbooks(Workbooks.Count).Activate
    > ActiveSheet.Unprotect
    >
    > Range("A1").Copy
    > Range("A1").PasteSpecial Paste:=xlPasteValues
    >
    > Range("B8:F17").Copy
    > Range("B8:F17").PasteSpecial Paste:=xlPasteValues
    >
    > Range("H8:I17").Copy
    > Range("H8:I17").PasteSpecial Paste:=xlPasteValues
    >
    > Range("B19:F20").Copy
    > Range("B19:F20").PasteSpecial Paste:=xlPasteValues
    >
    > Range("H19:I20").Copy
    > Range("H19:I20").PasteSpecial Paste:=xlPasteValues
    >
    > Range("A1").Copy
    > Range("A1").PasteSpecial Paste:=xlPasteValues
    >
    > Application.CutCopyMode = False
    >
    > Range("A1").Select
    >
    > ChDir "I:\Accounting\Daily Tonnes\DailyReports"
    > ActiveWorkbook.SaveAs Filename:= _
    > "I:\Accounting\Daily Tonnes\DailyReports\" & Range("Date") &
    > "-Sales.xls"
    >
    > ActiveWindow.Close
    >
    > 'MsgBox "File: I:\Accouting\Daily Tonnes\DailyReports\" & Range("Date") &
    > "-Sales.xls has been created", , "Daily Tonnes"
    >
    > 'If Not CBool(Len(Dir("I:\Accounting\Daily Tonnes\MonthlyReports\" &
    > Left(Range("Date"), 7) & ".xls"))) Then
    > 'End If
    >
    > ChDir "I:\Accounting\Daily Tonnes\MonthlyReports"
    > Workbooks.Open "I:\Accounting\Daily Tonnes\MonthlyReports\" &
    DaBookName
    > & ".xls"
    > Workbooks(DaBookName & ".xls").Activate
    > Sheets("Sheet1").Activate
    >
    > For Each c In Range("A5:A35")
    > If c.Text = DaDate Then
    > Range(Cells(c, c + 1)) = LevBR
    > Range(Cells(c, c + 2)) = LevPSE
    > Range(Cells(c, c + 3)) = LevPSW
    > Range(Cells(c, c + 4)) = LevWT
    > Range(Cells(c, c + 5)) = LevPW
    > Range(Cells(c, c + 6)) = VolHMC
    > Range(Cells(c, c + 7)) = VolPW
    > Range(Cells(c, c + 8)) = VolSR
    > Range(Cells(c, c + 9)) = VolFC
    > Range(Cells(c, c + 10)) = VOlWFC
    > Range(Cells(c, c + 11)) = VolWT
    > Range(Cells(c, c + 12)) = VolCR
    > Range(Cells(c, c + 13)) = VolWFT
    > Range(Cells(c, c + 14)) = VolDW
    > Range(Cells(c, c + 15)) = VolNMO
    > Range(Cells(c, c + 16)) = VolNMN
    > Else
    > End If
    > Next c
    >
    > Workbooks(DaBookName & ".xls").Close
    >
    > Workbooks("Daily Tonnes Model").Activate
    > Sheets("Main").Activate
    > Range("Date").Select
    > Application.ScreenUpdating = True
    >
    > End Sub
    >
    >
    >
    >
    >


    Reply With QuoteRegister To Reply
  3. 05-20-2005, 12:06 PM #3
    Damien McBain
    Guest

    Re: Type Mismatch Problem

    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    >c is a range object, not a column number or a row number.
    >
    > Try
    >
    > c.Offset(0,1).Value = LevBR
    >
    > etc.

    Thanks Bob, I saw my stupidity almost immediatelylafter I posted it, fell
    into my own wanker trap it would seem :/

    cheers for the response.

    Damo

    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Damien McBain" <[email protected]> wrote in message
    > news:[email protected]...
    >> What's wrong with this (I get t type mismatch error on the line with
    >> "Range(Cells(c, c + 1)) = LevBR")?
    >>
    >> Sub CreateSalesFile()
    >> Application.ScreenUpdating = False
    >> Dim DaDate As String
    >> Dim DaBookName
    >> Dim LevBR, LevPSE, LevPSW, LevWT, LevPW, VolHMC, VolPW, VolSR, VolFC,
    >> VOlWFC, VolWT, VolCR, VolWFT, VolDW, VolNMO, VolNMN
    >> LevBR = Range("bullring")
    >> LevPSE = Range("PugEast")
    >> LevPSW = Range("PugWest")
    >> LevWT = Range("White")
    >> LevPW = Range("PigmentShed")
    >> VolHMC = Sheets("Operations").Range("B10")
    >> VolPW = Sheets("Operations").Range("C10")
    >> VolSR = Sheets("Operations").Range("D10")
    >> VolFC = Sheets("Operations").Range("E10")
    >> VOlWFC = Sheets("Operations").Range("F10")
    >> VolWT = Sheets("Operations").Range("G10")
    >> VolCR = Sheets("Operations").Range("H10")
    >> VolWFT = Sheets("Operations").Range("I10")
    >> VolDW = Sheets("Operations").Range("J10")
    >> VolNMO = Sheets("Operations").Range("K10")
    >> VolNMN = Sheets("Operations").Range("L10")
    >> DaDate = Range("Date")
    >> DaBookName = Left(DaDate, 7)
    >>
    >> Sheets("Sales").Select
    >> Sheets("Sales").Copy
    >> Workbooks(Workbooks.Count).Activate
    >> ActiveSheet.Unprotect
    >>
    >> Range("A1").Copy
    >> Range("A1").PasteSpecial Paste:=xlPasteValues
    >>
    >> Range("B8:F17").Copy
    >> Range("B8:F17").PasteSpecial Paste:=xlPasteValues
    >>
    >> Range("H8:I17").Copy
    >> Range("H8:I17").PasteSpecial Paste:=xlPasteValues
    >>
    >> Range("B19:F20").Copy
    >> Range("B19:F20").PasteSpecial Paste:=xlPasteValues
    >>
    >> Range("H19:I20").Copy
    >> Range("H19:I20").PasteSpecial Paste:=xlPasteValues
    >>
    >> Range("A1").Copy
    >> Range("A1").PasteSpecial Paste:=xlPasteValues
    >>
    >> Application.CutCopyMode = False
    >>
    >> Range("A1").Select
    >>
    >> ChDir "I:\Accounting\Daily Tonnes\DailyReports"
    >> ActiveWorkbook.SaveAs Filename:= _
    >> "I:\Accounting\Daily Tonnes\DailyReports\" & Range("Date") &
    >> "-Sales.xls"
    >>
    >> ActiveWindow.Close
    >>
    >> 'MsgBox "File: I:\Accouting\Daily Tonnes\DailyReports\" & Range("Date") &
    >> "-Sales.xls has been created", , "Daily Tonnes"
    >>
    >> 'If Not CBool(Len(Dir("I:\Accounting\Daily Tonnes\MonthlyReports\" &
    >> Left(Range("Date"), 7) & ".xls"))) Then
    >> 'End If
    >>
    >> ChDir "I:\Accounting\Daily Tonnes\MonthlyReports"
    >> Workbooks.Open "I:\Accounting\Daily Tonnes\MonthlyReports\" &
    > DaBookName
    >> & ".xls"
    >> Workbooks(DaBookName & ".xls").Activate
    >> Sheets("Sheet1").Activate
    >>
    >> For Each c In Range("A5:A35")
    >> If c.Text = DaDate Then
    >> Range(Cells(c, c + 1)) = LevBR
    >> Range(Cells(c, c + 2)) = LevPSE
    >> Range(Cells(c, c + 3)) = LevPSW
    >> Range(Cells(c, c + 4)) = LevWT
    >> Range(Cells(c, c + 5)) = LevPW
    >> Range(Cells(c, c + 6)) = VolHMC
    >> Range(Cells(c, c + 7)) = VolPW
    >> Range(Cells(c, c + 8)) = VolSR
    >> Range(Cells(c, c + 9)) = VolFC
    >> Range(Cells(c, c + 10)) = VOlWFC
    >> Range(Cells(c, c + 11)) = VolWT
    >> Range(Cells(c, c + 12)) = VolCR
    >> Range(Cells(c, c + 13)) = VolWFT
    >> Range(Cells(c, c + 14)) = VolDW
    >> Range(Cells(c, c + 15)) = VolNMO
    >> Range(Cells(c, c + 16)) = VolNMN
    >> Else
    >> End If
    >> Next c
    >>
    >> Workbooks(DaBookName & ".xls").Close
    >>
    >> Workbooks("Daily Tonnes Model").Activate
    >> Sheets("Main").Activate
    >> Range("Date").Select
    >> Application.ScreenUpdating = True
    >>
    >> End Sub
    >>
    >>
    >>
    >>
    >>
    >
    >


    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1