Formulas

[finaljustice]

Hello all, this is probably a really quick fix but, i've been trying to insert a formula through vba into a cell.

Range("C2").Formula = "=MID(B2;FIND(" - ";B2;1)+2;LEN(B2)-FIND(" - ";B2;1)-5)"

For some reason I keep getting a error.
any suggestions to solve this?

Thanks a million.

 

[CWatts]

You have to use double quotes ("") to use quotes in strings.

VBA reads your code:

"=MID(B2;FIND(" - ";B2;1)+2;LEN(B2)-FIND(" - ";B2;1)-5)"

as "=MID(B2;FIND(" minus ";B2;1)+2;LEN(B2)-FIND(" minus ";B2;1)-5)"

Use single quotes at the beginning of the string and doubles within:

"=MID(B2;FIND("" - "";B2;1)+2;LEN(B2)-FIND("" - "";B2;1)-5)"

 

[finaljustice]

You have to use double quotes ("") to use quotes in strings.

VBA reads your code:

"=MID(B2;FIND(" - ";B2;1)+2;LEN(B2)-FIND(" - ";B2;1)-5)"

as "=MID(B2;FIND(" minus ";B2;1)+2;LEN(B2)-FIND(" minus ";B2;1)-5)"

Use single quotes at the beginning of the string and doubles within:

"=MID(B2;FIND("" - "";B2;1)+2;LEN(B2)-FIND("" - "";B2;1)-5)"

Hi thanks for replying, but now im getting a new error.
I'm translating this the best I can but the msg is:
"Error on execution time '1004':
Error of defining an application or defining an object."

Here is my macro:

Sub teste()
     Range("A1").Select
     Range("C2").Formula = "=MID(B2;FIND("" - "";B2;1)+2;LEN(B2)-FIND("" - "";B2;1)-5)"
 
End Sub

 

[CWatts]

I didn't even notice this late last night... but you used semicolons.

Excel formulas have their arguments separated by commas, just replace the semicolons with commas.

 

[finaljustice]

I didn't even notice this late last night... but you used semicolons.

Excel formulas have their arguments separated by commas, just replace the semicolons with commas.

Perfect, that works perfectly! I thought that I should use the same separators as the one used in cells, ergo the ";".

Now I got it. Thanks alot!

 

[finaljustice]

I didn't even notice this late last night... but you used semicolons.

Excel formulas have their arguments separated by commas, just replace the semicolons with commas.

Hi could you shine a light for me on a quick issue? This is an exmple:

Sub formulas()
'Determine the range
x = ActiveWorkbook.Sheets.Count
Sheets(x).Select
ws = ActiveSheet.Name
blimit = Range("D1048576").End(xlUp).Address
blimit = Right(blimit, 3)
Sheets(1).Select
Range("A27").Select
Do While ActiveCell = Empty
varlookup = ActiveCell.Address
linha = Trim(Right(varlookup, 2))
Range("$D$" & linha).Formula = "=Iferror(VLookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ", 2, False),"""")" 
Range("$K$" & linha).Formula = "=Iferror(VLookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ", 31, False),"""")"
Range("$M$" & linha).Formula = "=Iferror((VLookup(" & varlookup & ", " & ws & "!$D$6:$AR$" & blimit & ", 7, False)*"$K$" " & linha & " ,"""")"  'this is where I am having an issue. Now I am trying to multiply by cell $K$&linha (linha = the line variable), this is where the issue happens: *"$K$" " & linha & "
ActiveCell.Offset(1, 0).Select


Loop


End Sub

Any idea how to code it, i've been trying to do this for sometime.