CODEWITHSUNDEEP
vbaexcel
J Doe
J Doe

Reputation: 65

Pasting a formula if value exists in range

I'm trying to create a macro that will paste a formula into column I if a value exists in column H.

This piece of code is where I'm having problems. When I run it, it says Run-time error '1004': Method 'Range' of object '_Global' failed. I know this isn't the most efficient way of accomplishing what I want to do though. It should only paste that formula if a value exists in column H rather than indiscriminately. Does anyone know how to do this?

Range("I2:I" & LastRow).Formula = "=CONCATENATE(""Ck #"", G2,"" - "", H2)"

Full code:

Sub prepn()

Columns("I:L").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").Value = "Description"
Range("J1").Value = "Co"
Range("K1").Value = "Au"
Range("L1").Value = "Acct"
Range("M1").Value = "Comments"
Range("I2:I" & LastRow).Formula = "=CONCATENATE(""Ck #"", G2,"" - "", H2)"
End Sub

Thank you in advance!

Upvotes: 0

Views: 279

Answers (2)

mooseman
mooseman

Reputation: 2017

Here is what I think you want. Last row calc is for column I, the one you are putting data, if not just change the letter 

  Sub prepn()

    LastRow = Range("I" & Rows.Count).End(xlUp).Row
    Columns("I:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I1").Value = "Description"
    Range("J1").Value = "Co"
    Range("K1").Value = "Au"
    Range("L1").Value = "Acct"
    Range("M1").Value = "Comments"
    Range("I2:I" & LastRow).Formula = "=CONCATENATE(" & Chr(34) & "Ck #" & Chr(34) & ", G2," & Chr(34) & " - " & Chr(34) & ", H2)"

End Sub

With g2 = Here and H2 = Also you get This in the cell =CONCATENATE("Ck #", G2," - ", H2)

I'm betting you don't want G2 and H2 for every cell, but that is another question.

Upvotes: 1

Sam
Sam

Reputation: 5731

The problem is most likely your quotes in the text. They must be written as "" to result in ". Something like this:

Range("I2:I" & LastRow).Formula = "=CONCATENATE(""Ck #"", G2,"" - "", H2)"

Upvotes: 0

Related Questions