Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch with 2 Variables
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 --------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch with 2 Variables
Hi
Try declaring dp and pn as Variants. regards Paul On Oct 19, 6:55*pm, Ryan wrote: 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 --------------------------------------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch with 2 Variables
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" wrote: 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 --------------------------------------------------------------------------- . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch with 2 Variables
On Oct 20, 5:34*am, Patrick Molloy
wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch with 2 Variables
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" wrote: On Oct 20, 5:34 am, Patrick Molloy wrote: 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" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
Type mismatch using rnge as Range with Type 8 Input Box | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
type mismatch--how to fix | Excel Discussion (Misc queries) | |||
type mismatch | Excel Programming |