Type Mismatch with 2 Variables

[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

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

 

[paul.robinson]

Hi
Try declaring dp and pn as Variants.
regards
Paul

 

[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

 

[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

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

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

 

[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'

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

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

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
.