Type Mismatch with 2 Variables

R

Ryan

Hi...I'm trying to fill in a cell with the formula:
=IF(COUNT(RPM),LOOKUP(MIN(RPM),RPM,Worksheet!A:A),LOOKUP(IF(COUNTA
(RPM),RPM,Worksheet!A:A),"",""))

My Dept range = A:A
My RPM range = a column on another sheet

I was able to get this working before with a single variable declared,
then when I realized I needed a second variable I started getting a
type mismatch error. Are you allowed to declare 2 variables like I
did? I'm not sure why I can't get this working. Any help would be
greatly appreciated. Thanks!

-----------------------------------------------------------------------

Sub test()

Dim dp As String
Dim pn As String

dp = Sheets("Worksheet").Range("Dept").Value
pn = Sheets("Dashboard").Range("Contract").Value

Sheets("Dashboard").Range("Notes").Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=IF(COUNT(" & pn & "),LOOKUP(MIN(" & pn &
")," & pn & "," & dp & "),LOOKUP(IF(COUNTA(" & pn & ")," & pn & "," &
dp & "),"""",""""))"

End Sub

---------------------------------------------------------------------------
 
P

Patrick Molloy

I think your declarations are ok
what values do you have in dp and pn when you step thorugh the code?

I get this formula
=IF(COUNT(RPN),LOOKUP(MIN(RPN),RPN,Worksheet!A:A),LOOKUP(IF(COUNTA(RPN),RPN,Worksheet!A:A),"",""))

which matches yours when
worksheet!Dept := Worksheet!A:A
Dashboard!Contract := RPN
 
R

Ryan

I think your declarations are ok
what values do you have in dp and pn when you step thorugh the code?

I get this formula
=IF(COUNT(RPN),LOOKUP(MIN(RPN),RPN,Worksheet!A:A),LOOKUP(IF(COUNTA(RPN),RPN,Worksheet!A:A),"",""))

which matches yours when
worksheet!Dept := Worksheet!A:A
Dashboard!Contract :=  RPN

Ryan said:
Hi...I'm trying to fill in a cell with the formula:
=IF(COUNT(RPM),LOOKUP(MIN(RPM),RPM,Worksheet!A:A),LOOKUP(IF(COUNTA
(RPM),RPM,Worksheet!A:A),"",""))
My Dept range = A:A
My RPM range = a column on another sheet
I was able to get this working before with a single variable declared,
then when I realized I needed a second variable I started getting a
type mismatch error. Are you allowed to declare 2 variables like I
did? I'm not sure why I can't get this working. Any help would be
greatly appreciated. Thanks!

Sub test()
Dim dp As String
Dim pn As String
dp = Sheets("Worksheet").Range("Dept").Value
pn = Sheets("Dashboard").Range("Contract").Value
Sheets("Dashboard").Range("Notes").Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(COUNT(" & pn & "),LOOKUP(MIN(" & pn &
")," & pn & "," & dp & "),LOOKUP(IF(COUNTA(" & pn & ")," & pn & "," &
dp & "),"""",""""))"

Thanks for the reply Patrick. I'm not suite sure i know how to step
through the code properly, but the end result you posted is what I
need (except RPN is RPM). I had explicitly called our Worksheet!A:A
previously and everything worked fine. It was only when I tried to
make that a range and include 2 variables that I started getting the
type mismatch. Declaring my variables as variants didn't make any
difference. Not sure where to go from here.

Sub test()

Dim pn As String
Dim dp As String

pn = Sheets("Dashboard").Range("Contract").Value
dp = Sheets("Worksheet").Range("RPM").Value

Sheets("Dashboard").Range("Notes").Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=IF(COUNT(" & pn & "),LOOKUP(MIN(" & pn &
")," & pn & "," & dp & "),LOOKUP(IF(COUNTA(" & pn & ")," & pn & "," &
dp & "),"""",""""))"

End Sub
 
P

Patrick Molloy

In the VBA editor, select the code then press F8
Each time you do so you well execute the next step in the code...you will
see a yellow marker to indicate the next lline to be excuted.
In the immediate window you can check value by ising ? forllowed by the
exopression. ? is shortcode for 'print'




Ryan said:
I think your declarations are ok
what values do you have in dp and pn when you step thorugh the code?

I get this formula
=IF(COUNT(RPN),LOOKUP(MIN(RPN),RPN,Worksheet!A:A),LOOKUP(IF(COUNTA(RPN),RPN,Worksheet!A:A),"",""))

which matches yours when
worksheet!Dept := Worksheet!A:A
Dashboard!Contract := RPN

Ryan said:
Hi...I'm trying to fill in a cell with the formula:
=IF(COUNT(RPM),LOOKUP(MIN(RPM),RPM,Worksheet!A:A),LOOKUP(IF(COUNTA
(RPM),RPM,Worksheet!A:A),"",""))
My Dept range = A:A
My RPM range = a column on another sheet
I was able to get this working before with a single variable declared,
then when I realized I needed a second variable I started getting a
type mismatch error. Are you allowed to declare 2 variables like I
did? I'm not sure why I can't get this working. Any help would be
greatly appreciated. Thanks!

Sub test()
Dim dp As String
Dim pn As String
dp = Sheets("Worksheet").Range("Dept").Value
pn = Sheets("Dashboard").Range("Contract").Value
Sheets("Dashboard").Range("Notes").Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(COUNT(" & pn & "),LOOKUP(MIN(" & pn &
")," & pn & "," & dp & "),LOOKUP(IF(COUNTA(" & pn & ")," & pn & "," &
dp & "),"""",""""))"

Thanks for the reply Patrick. I'm not suite sure i know how to step
through the code properly, but the end result you posted is what I
need (except RPN is RPM). I had explicitly called our Worksheet!A:A
previously and everything worked fine. It was only when I tried to
make that a range and include 2 variables that I started getting the
type mismatch. Declaring my variables as variants didn't make any
difference. Not sure where to go from here.

Sub test()

Dim pn As String
Dim dp As String

pn = Sheets("Dashboard").Range("Contract").Value
dp = Sheets("Worksheet").Range("RPM").Value

Sheets("Dashboard").Range("Notes").Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=IF(COUNT(" & pn & "),LOOKUP(MIN(" & pn &
")," & pn & "," & dp & "),LOOKUP(IF(COUNTA(" & pn & ")," & pn & "," &
dp & "),"""",""""))"

End Sub
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top