VBA concatenate

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Dear sir or madam

Please see my screenshots below:
1636295904885.png

1636295915622.png

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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
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.
Rich (BB code):
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

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

1636300644222.png




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?

1636300542478.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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?

1636302965969.png


1636303077966.png
 

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?

1636304023962.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Solution

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Forum statistics

Threads
1,148,397
Messages
5,746,457
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top