Hi, I'd like to make an input box pop up with a default value on it,
that should be stored in a variable, and that any information on this
would then be inserted on a cell like an excel formula.
Questions a
1. How to make an inputbox pop up with a default value?
2. How to insert a formula on a cell like it was inserted in excel and
not vba?
VBA Style: ActiveCell.FormulaR1C1 = _
"=IF(PedidosImp!RC[-29]="""",REPT(""
"",25),TEXT(PedidosImp!RC[-26],""0000000,00""))"
I'd like to insert a formula like:
=IF(PedidosImp!F1="";REPT(" ";25);TEXT(PedidosImp!I1;"0000000,00"))

Without that extra quoting.

Thanks in advance!

Lunks,

1:

Dim myVal As Variant
myVal = Application.InputBox("Enter a value", _
"My Input", "Default Value")
MsgBox myVal


2:

You need the doubled-up double quotes to tell Excel that they are to be put into the formula. No
way around it.

HTH,
Bernie
MS Excel MVP


"Lunks" wrote in message
oups.com...
Hi, I'd like to make an input box pop up with a default value on it,
that should be stored in a variable, and that any information on this
would then be inserted on a cell like an excel formula.
Questions a
1. How to make an inputbox pop up with a default value?
2. How to insert a formula on a cell like it was inserted in excel and
not vba?
VBA Style: ActiveCell.FormulaR1C1 = _
"=IF(PedidosImp!RC[-29]="""",REPT(""
"",25),TEXT(PedidosImp!RC[-26],""0000000,00""))"
I'd like to insert a formula like:
=IF(PedidosImp!F1="";REPT(" ";25);TEXT(PedidosImp!I1;"0000000,00"))

Without that extra quoting.

Thanks in advance!

I'm getting "Compile Error: Expected: list separator or )"
With the following formula:
UF = InputBox("Aperte Enter.","
","=IF(PedidosImp!RC[-20]="""",IF(PedidosImp!RC[-11]=""ML"",""MIL"" &
REPT("" "",2),IF(PedidosImp!RC[-11]=""PT"",""PK"" & REPT(""
"",3),IF(PedidosImp!RC[-11]=""CL"",""CT"" & REPT(""
"",3),IF(PedidosImp!RC[-11]=""LT"",""L"" & REPT(""
"",4),IF(PedidosImp!RC[-11]=""PR"",""PAR"" & REPT(""
"",2),IF(PedidosImp!RC[-11]=""MT"",""M"" & REPT(""
"",4),PedidosImp!RC[-11] _
&REPT("" "",5-LEN(PedidosImp!RC[-11])))))),REPT("" "",10))"

Lunks,

Your formula is too long - Inputboxes are limited to 255 characters:

Dim myStr As String
Dim UF As String
myStr = "=IF(PedidosImp!RC[-20]="""",IF(PedidosImp!RC[-11]=""ML"",""MIL"" & " & _
"REPT("" "",2),IF(PedidosImp!RC[-11]=""PT"",""PK"" & REPT("" "",3)," & _
"IF(PedidosImp!RC[-11]=""CL"",""CT"" & REPT("" "",3)," & _
"IF(PedidosImp!RC[-11]=""LT"",""L"" & REPT("" "",4)," & _
"IF(PedidosImp!RC[-11]=""PR"",""PAR"" & REPT("" "",2)," & _
"IF(PedidosImp!RC[-11]=""MT"",""M"" & REPT("" "",4)," & _
"PedidosImp!RC[-11] & REPT("" "",5-LEN(PedidosImp!RC[-11])))))), REPT("" "",10))"

MsgBox Len(myStr)

UF = InputBox("Aperte Enter.", "", myStr)

MsgBox Len(UF)

HTH,
Bernie
MS Excel MVP


"Lunks" wrote in message
oups.com...
I'm getting "Compile Error: Expected: list separator or )"
With the following formula:
UF = InputBox("Aperte Enter.","
","=IF(PedidosImp!RC[-20]="""",IF(PedidosImp!RC[-11]=""ML"",""MIL"" &
REPT("" "",2),IF(PedidosImp!RC[-11]=""PT"",""PK"" & REPT(""
"",3),IF(PedidosImp!RC[-11]=""CL"",""CT"" & REPT(""
"",3),IF(PedidosImp!RC[-11]=""LT"",""L"" & REPT(""
"",4),IF(PedidosImp!RC[-11]=""PR"",""PAR"" & REPT(""
"",2),IF(PedidosImp!RC[-11]=""MT"",""M"" & REPT(""
"",4),PedidosImp!RC[-11] _
&REPT("" "",5-LEN(PedidosImp!RC[-11])))))),REPT("" "",10))"

I'm getting error on the following part of the program:
Range("Z1").Select
ActiveCell.FormulaR1C1 = myStr
Selection.Copy
Range("Z1:Z" & ncl).PasteSpecial

Error: Run-time error '1004':
Application-defined or object-defined error

myStr is exactly as you posted.