VBA concatenate

[vbanewbie68]

Dear sir or madam

Please see my screenshots below:

VBA Code : Import.Cells(x, GiftInMemoryA).Value = Cells(x, CelebrationType).Value

Question:

I would like to know how to use a concatenate in VBA to combine Birthday – David and Sam – 30-Sep-21 into an one column of Gift Attri In Memory of 2

For example Celebratation Type & “-“ & CelebrationTitle & “-“ & CelebrationDate

Thank you

Best regards
vbanewbie68

 

[Joe4]

For example Celebratation Type & “-“ & CelebrationTitle & “-“ & CelebrationDate

You can concatenate in VBA the same way you do in Excel, with the "&" like that.
However, if you concatenate a valid date or number value, you will get the unformatted value (the numeric representation of the date), unless you apply the formatting function, i.e.

Celebratation Type & “-“ & CelebrationTitle & “-“ & FORMAT(CelebrationDate, "dd-mmm-yy")

Also, I see it looks like you are using "Import" as a worksheet variable name. It is HIGHLY recommended that you do NOT use reserved words (words that are already use by VBA as functions, methods, properties, etc) as it can cause ambiguity, errors, and unexpected results. If you aren't sure if a word is a "reserved" word or not, one way that often shows that is to type the word on a new line in a VBA procedure all in lower case, i.e. "import". If VBA automatically capitalizes it, i.e., "Import", then it is most likely a reserved word and you should not use it as one of your variables or name of procedure or function.

 

[vbanewbie68]

Thank you for replying my messgae
I still get an error Compile error. Syntax error. Please see my VBA line.

Import.Cells(x, GiftAttrInMemoryof2).Value = Cells(x, CelebrationType&"-"&CelebrationTitle&"-"&FORMAT(CelebrationDate,"dd-mmm-yy").Value

For Import as a worksheet. This is what I have been advised by one of my colleague. Should it be rename to something else instead?

 

[jasonb75]

For Import as a worksheet. This is what I have been advised by one of my colleague. Should it be rename to something else instead?

Using Import as a variable is not a good idea, if you want something close then maybe misspell it as Imprt instead so that it is not the same as a reserved word. Anyone who advises using a reserved word as a variable needs a bit of re-educating

Not exactly sure what you're trying to do on the line that came up red but correct syntax would be more like

With Imprt
    .Cells(x, GiftAttrInMemoryof2).Value = .Cells(x, CelebrationType).Value & "-" & .Cells(x, CelebrationTitle).Value & "-" & FORMAT(.Cells(x, CelebrationDate).Value, "dd-mmm-yy")
End With

This is assuming that all of the variables are valid. As you have not shown us the definitions, we can only assume that they are.

 

[vbanewbie68]

Thank you! Will take your advice re: import as NOT a variable.

See the screenshot as it works perfectly. The only issue is that how to remove two minus for each cell?

 

[vbanewbie68]

The only I can think of is to add a new line for VBA to create IF and then? Please see my screenshot below.

If the value is exist then then to use concatenate to combine Celebration Type, CelebrationTitle and CelebrationDate onto the Gift Attri In Memory of 2?

 

[jasonb75]

Try this instead. Note that this will not remove any -- that are already there, but it should skip the row without entering them if there is no other data.

With Imprt
    If .Cells(x, CelebrationType).Value <> "" Then .Cells(x, GiftAttrInMemoryof2).Value = .Cells(x, CelebrationType).Value & "-" & .Cells(x, CelebrationTitle).Value & "-" & FORMAT(.Cells(x, CelebrationDate).Value, "dd-mmm-yy")
End With

 

[vbanewbie68]

Thank you for this and I am so pleased that your last VBA code is absolutely working! Thank you for your time on this matter which is much appreciated . Again I agree with you on 'Import'. Have a great day!