Why am I getting a mismatch Error

bigck2

Board Regular
Joined
Feb 20, 2014
Messages
147
Hello,

I keep getting a mismatch error, but i can't figure out why. Here is the code:

Sub Report()


Dim LR As Long
Dim LC As Long
Dim Rooms As Long
Dim ADR As Long
Dim Nights As Long
Dim r As Long
Dim c As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(16, Columns.Count).End(xlToRight).Column

For r = 18 To LR
For c = 17 To LC
If Cells(r, c).Value = "Here" Then
Rooms = Cells(r, 14).Value
ADR = Cells(r, 16).Value
Nights = Cells(r, 12).Value

Range(Cells(r, c), Cells(r, (c + Nights))).Value = Rooms * ADR
End If
Next c
Next r


End Sub


When I try to run I get Run-time error '13': Type mismatch.

If I click debug, it highlights the row: If Cells(r,c).Value = "Here" Then


I have also tried using: If Cells(r,c).Value = 1 Then

And changed the formula in my worksheet to supply '1' instead of 'Here'.

I can't figure this out. It makes sense to me.

Thanks for any help.

Chris
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Well not sure if this is part of the issue, but shouldn't this:
LC = Cells(16, Columns.Count).End(xlToRight).Column

be this instead:
LC = Cells(16, Columns.Count).End(xlToLeft).Column
 
Upvote 0
Also, LC and C should be declared as Integer, not Long
 
Upvote 0
Hey guys,

Thanks for the input.

Ragnar1211, you were definitely correct about my LC variable.

rintaro, you're suggestion worked. However, I think I was trying to loop through way to many cells. I ended up changing my code to:

Sub Report()


Dim LR As Long
Dim LC As Long
Dim Rooms As Long
Dim ADR As Long
Dim Nights As Long
Dim r As Long
Dim c As Long
Dim RealDates As Range

LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(16, Columns.Count).End(xlToRight).Column


Range("Q17").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeFormulas, 7).Select

Set RealDates = Selection

For Each Cell In RealDates
r = Cell.Row
c = Cell.Column
Rooms = Cells(r, 14).Value
ADR = Cells(r, 16).Value
Nights = Cells(r, 12).Value

Range(Cells(r, c), Cells(r, (c + Nights - 1))).Value = Rooms * ADR
Next Cell




End Sub
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top