Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Type Mismatch Problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default 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 QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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" wrote in message
...
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 QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 49
Default Type Mismatch Problem
"Bob Phillips" wrote in message
...
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 ****** trap it would seem :/

cheers for the response.

Damo

--

HTH

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


"Damien McBain" wrote in message
...
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 QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Type Mismatch [email protected] Excel Worksheet Functions 1 May 16th 07 03:29 PM
type mismatch--how to fix rroach Excel Discussion (Misc queries) 2 July 14th 05 06:23 PM
Type mismatch problem? NooK[_45_] Excel Programming 3 August 4th 04 01:07 PM
Type mismatch Steve Garman Excel Programming 0 February 5th 04 07:39 AM
Type Mismatch Phil Hageman[_3_] Excel Programming 2 January 9th 04 06:11 PM


All times are GMT +1. The time now is 03:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement