insert a formula with varying parameteres - error 13 type mismatch

0
votes

I'm quite new in programming and I'm a self-taught in this field (copying lots of code lines from internet, mostly from here!).

I have to insert some calculations in new sheets and, between them, a vlookup formula where in each sheet the table range changes. As I'm a beginner in code writing, when I need to write something new I use to record a macro, then I modify it 'till I reach my goal.

In this case I recorded the formula:

ActiveCell.FormulaR1C1 = "=VLOOKUP(R[16]C[-6],R[14]C[-6]:R[19]C[-5],2,TRUE)"

and modified it as:

Dim n, i As Integer
Dim mCell As Range

n = 7
i = 13726
Set mCell = ActiveSheet.Cells(3 + n + 8, 2)

Cells(4, 15).Formula = "=VLOOKUP(" & Cells(3, 15) & ";" & Range(Cells(3 + n + 8, mCell.Column), Cells(i - 1, n + 1)) & ";2;TRUE)"

before to insert it in the main macro. This is a simplified cut of the macro (I fixed the variables, where in the original one they will change for each new workbook).

When I run it I receive an "error 13 - type mismatch". Looking for answers and doing a lot of "try&error", I checked it again and again and I changed quite a lot of pieces of the formula:

  • Cells(4,15) / Range("O4")
  • .Formula / .FormulaLocal / .Value
  • "=VLOOKUP(" / "=BUSCARV(" --> I'm working with excel in spanish, in the sheet the functions are traslated
  • Cells(3,15) and Range(...) / Cells(3,15).Address and Range(...) / Cells(3,15).Address and Range(...).Address
  • , / ; --> in the sheet I have to use ";", in VBA is recorded automatically as ","
  • TRUE / VERDADERO --> still the spanish point

I've written all the permutations (or combinations? :-P ) and run subsiquently all them (144 variations), always receiving the same error type.

So, probably the problem is elsewhere... what am I missing? thank you so much!!

1
excelformulavlookupvba
There is no need to use Integer over Long: Integers vs Longs 32-bit vs 64-bit – Ralph

1 Answers

2
votes

Excel is English oriented, if you are using the Spanish version, then first you need to change from Formula to FormulaLocal. When using in a formula ; instead of , as a delimiter between the Function parameters, you will need to use FormulaLocal.

Second, you dim Dim n, i As Integer , this means i As Integer while n As Variant, you need to define Dim n As Long, i As Long.

Code

Cells(4, 15).FormulaLocal = "=VLOOKUP(" & Cells(3, 15).Address & ";" & Range(Cells(3 + n + 8, mCell.Column), Cells(i - 1, n + 1)).Address & ";2;TRUE)"