type mismatch error

[KeithGrindle]

I am getting a type mismatch error when running this code. It goes to the input box and then after a number is entered into the input box I get a type mismatch error. Any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrorHandler

Dim Width As Double
Dim Height As Double
Dim Cellnum As Integer
Dim myrange As Range



If Not Intersect(ActiveCell, Range("WallA")) Is Nothing Then

If ActiveCell.Text = "HWT" Or ActiveCell.Text = "HWA" Then
Width = Application.InputBox("Please enter the width for this HVAC Window", "HVAC Window Width", Type:=1)
Cellnum = ActiveCell.Range("WallA").Rows - 60
Worksheets("Formulas").Range("ValuesWallA").Row(Cellnum).Value = Width

Else

If ActiveCell.Text = "CW" Then

Width = Application.InputBox("Please enter the width for this Custom Window", "Custom Window Width", Type:=1)
Cellnum = ActiveCell.Range("WallA").Rows - 60
Worksheets("Formulas").Range("ValuesWallA").Row(Cellnum).Value = Width
Height = Application.InputBox("Please enter the width for this Custom Window", "Custom Window Height", Type:=1)
Cellnum = ActiveCell.Range("WallA").Rows - 60
Worksheets("Formulas").Range("WallAWinandDrHeight").Row(Cellnum).Value = Height

Else

If ActiveCell.Text = "CF" Then

Width = Application.InputBox("Please enter the width for this Custom Fill Panel", "Custom Fill Width", Type:=1)
Cellnum = ActiveCell.Range("WallA").Rows - 60
Worksheets("Formulas").Range("ValuesWallA").Rows(Cellnum).Value = Width
Height = Application.InputBox("Please enter the width for this Custom Fill Panel", "Custom Fill Height", Type:=1)
Cellnum = ActiveCell.Range("WallA").Rows - 60
Worksheets("Formulas").Range("WallAWinandDrHeight").Row(Cellnum).Value = Height
Else

If ActiveCell.Text <> "HWA" Or ActiveCell.Text <> "HWT" Or ActiveCell.Text <> "CW" Or ActiveCell.Value <> "CF" Then

Cellnum = ActiveCell.Range("WallA").Rows - 60
Worksheets("Formulas").Range("ValuesWallA").Row(Cellnum).Formula = "=VLOOKUP(DN(cellnum),PartsList,3,FALSE)"


End If

End If
End If
End If
End If

Exit Sub
ErrorHandler: MsgBox "Error number: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub





Thanks,

Keith

[Tom Schreiner]

Hi
At a glance your code looks fine...
You are, however, using words, namely, "Width" and "Height", which VBA uses for property assignments for many different objects.
I would change the name of these variables to start.
If that does not solve your problem then we'll take it from there.
Tom

[Ivan F Moala]

Your statement:
'
'

ActiveCell.Range("WallA").Rows - 60

'
'
Is returning a Range Object and NOT an Integer....should change this to;
'
'

ActiveCell.Range("WallA").Rows.Count - 60

_________________
Kind Regards,
Ivan F Moala
Have a Nice day

[ This Message was edited by: Ivan F Moala on 2002-05-16 00:11 ]

[Brian]

1) I tried the input box alone and it worked fine. no data mismatch there.

Sub try()
Dim Width As Double
Width = Application.InputBox("Please enter the width for this HVAC Window", "HVAC Window Width", Type:=1)
MsgBox "width = " & Width
End Sub

2) Your next line:

Cellnum = ActiveCell.Range("WallA").Rows - 60

is a bit perplexing to me because "ActiveCell" is by definition only one cell so ActiveCell.rows must always equal 1. I don't understand how the range fits in there. I presume that perhaps you want to know where the ActiveCell is within the Range("WallA") If yes, then you could use Range("WallA").row - ActiveCell.row.

3) When I name a range "TestRange" and select a cell in that range, then the following code gives a "type mismatch" error. I suspect that this is your problem.

'Option Explicit
Sub test2()
Dim Cellnum As Integer
Cellnum = ActiveCell.Range("RangeTest").Rows
MsgBox "Cellnum = " & Cellnum
End Sub

When i commented out the Dim statement and opened the "locals" window, Cellnum turned out to be a variable other than an integer, indicating that the formula does not return an integer.

4) Suggestion: For debugging, comment out your error handler so that the normal VBA error message can bring you directly to the incorrect line.

PS: From excel help:

Be careful to distinguish between the active cell and the selection. The active cell is a single cell inside the current selection. The selection may contain more than one cell, but only one is the active cell.

[Brian]

Ivan,

You beat me to it, but I get a "Invalid Qualifier" error that highlights "row" in your code.

[Ivan F Moala]

On 2002-05-16 00:11, Brian wrote:
Ivan,

You beat me to it, but I get a "Invalid Qualifier" error that highlights "row" in your code.

Hi Brian....one of the things about this board is that while you a busy with answering one Question, so are others and you
usually get caught ie you finish the Q then post and find someone else has answered JUST
before you .......you are correct on this
point BUT if you look again you will find
that I utilised the Boards Edit function and
changed the code .....thanks for that.

[KeithGrindle]

I've changed Width and Height to be Wid an Hght so that should help.

The line:
Cellnum = ActiveCell.Range("WallA").Rows - 60
was set to - 60 because for some reason cell numb was returning 61 when this code was running. What I want is whatever position the activecell is then I want to put the value of wid or height in that same position in either the range("ValuesWallA") or range("WallAWinandDRHeight") depending on whether the input is wid or hght.

I change the line to Cellnum = ActiveCell.Range("WallA").Rows.Count and I am now getting a runtime error 451: Property let procedure not defined and property get procedure did not return an object. It points
to this line: Worksheets("Formulas").Range("ValuesWallA").Row(Cellnum).Value = Wid

If I change it to rows(cellnum) then the input box will not go away. I am thinking that maybe it is looking for a range object but I am not to sure. Any help would be greatly appreciated.

Thanks for the previous help and thanks in advance for any future help on this.

Keith