ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Type Mismatch Problem (https://www.excelbanter.com/excel-programming/329809-type-mismatch-problem.html)

Damien McBain[_2_]
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

Bob Phillips[_6_]
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

Damien McBain[_2_]
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


All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com