VBA Type Mismatch Error (Error 13)

[Boogerbut74]

I am very new to macros and VBA coding; right now, I'm having trouble with the code below. I'm trying to get it to do any row without changing the equation every time. So the C+ Active Cell.Row gets put in as an actual cell, same with F, then it's just looking at the q3 batch in these coulombs to find the exact match or next highest and gives me the data I want. Right now, though, I keep getting VBA Type Mismatch Error (Error 13), and I don't know how to fix it. I've been trying for a couple of hours now, and I'm just going down rabbit holes. I'm assuming my problem comes from quoting the C out and adding the ActiveCell.Row but I don't know how else to do this. I've put the line that's giving me problems bellow in red.








Sub Gmacro()
'
' Gmacro Macro
'

'
ActiveCell.Formula = "=XLOOKUP(C" + ActiveCell.Row + " & F" + ActiveCell.Row + ",'Q3 Batch Historian'!$A$1:A5045&'Q3 Batch Historian'!$C$1:C5045,ROW('Q3 Batch Historian'!$C$1:C5045), N/A,0,1)"


Range(ActiveCell).Select
End Sub

 

[Micron]

You usually don't add ( + ) strings - you concatenate them - &
I presume that range references need to be either bracketed [ ] or enclosed in quotes like "=XLOOKUP("C3", ) so literal quotes have to be doubled (or some expression allow the use single quotes, which makes it easier to write) like "=XLOOKUP('C'" & ActiveCell.Row & "'F'" & ActiveCell.Row ...
In these cases, sometimes the issue is that you need .Value when referring to a range, otherwise you can be passing the range address to the expression. That will certainly raise the error you're getting.

Suggest you get a formula to work in the sheet first then try concatenating references and variables in code. If you can't concatenate in code correctly, at least you could post a working formula.

 

[steve the fish]

The problem is you need the ampersand not a plus. Also N/A will need to be ""N/A"" if you want N/A in the cell if not found. All that said you wont need a loop (which i presume you are intending to use) to add that formula to lots of cells. A loop would be slow.

 

[Boogerbut74]

Thank you all!