CODEWITHSUNDEEP
vbaexcel
whada
whada

Reputation: 316

VBA - combining strings to create a formula

I'm trying to create a formula in vba that would ask the user to click on a cell and then use the address of that cell in the formula. My approach, which some may laugh at, is to break the formula into strings and then concatenate it back into final formula string once I know the address of the cell the user clicks on. For some reason, I get an error on activecell.value line despite the fact that debug print shows the correct formula. Any help with this is greatly appreciated, see below:

Dim p1, p2, p3, p4, p5, p6, p7 As String
    p1 = "=IF(ISERROR(MID("
    p2 = ",FIND(OP-,"
    p3 = "),10)),"
    p4 = ",(MID("
    p5 = ",FIND(OP-,"
    p6 = "),10)))"

Dim cellClick As Range
    Set cellClick = Application.Selection
    Set cellClick = Application.InputBox("Select Range :", xTitleId, cellClick.Address, Type:=8)

Dim vz, finVz As String
    vz = cellClick.Address
    finVz = p1 & vz & p2 & vz & p3 & vz & p4 & vz & p5 & vz & p6

ActiveCell.Value = finVz

Upvotes: 0

Views: 352

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

=IF(ISERROR(MID($J$11,FIND(OP-,$J$11),10)),$J$11,(MID($J$11,FIND(OP-,$J$11),10))) is the output.

it is missing "" around the OP-

Also it needs to be ActiveCell.Formula, as @Nathan_Sav stated.

Change to:

Dim p1 As String, p2 As String, p3 As String
Dim p4 As String, p5 As String, p6 As String, p7 As String
    p1 = "=IF(ISERROR(MID("
    p2 = ",FIND(""OP-"","
    p3 = "),10)),"
    p4 = ",(MID("
    p5 = ",FIND(""OP-"","
    p6 = "),10)))"

Dim cellClick As Range
    Set cellClick = Application.Selection
    Set cellClick = Application.InputBox("Select Range :", xTitleId, cellClick.Address, Type:=8)

Dim vz, finVz As String
    vz = cellClick.Address
    finVz = p1 & vz & p2 & vz & p3 & vz & p4 & vz & p5 & vz & p6

ActiveCell.Formula = finVz

Upvotes: 1

Related Questions