• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate Two Text in VBA

Debraj

Debraj

Excel Ninja
Please help me to set the below code..


Actual URL is

Code: 
http://www.SomeWebsite.com/catalog/?q=[b]AR271WA85RRAINDFAS[/b][code]&submit=&baseUrl=


where bold part is changeable..


So in BJ2 formula should be

="http://www.SomeWebsite.com/catalog/?q="&B2&"&submit=&baseUrl="[/code]


Can Someone help me to to put (") at correct place in the below code..


<span style="color:rgb(255,0,0);"> Range("bj2:bj" & lrinVD).Formula = "="http://www.SomeWebsite.com/catalog/?q=" & B2 & "&submit=&baseUrl=""</span>
 
Hi, Debraj Roy!


Where are you going to use that code?


Regards!


EDIT:

Sorry, markups confused me, try this:

-----

[pre]
Code: 
For z = 2 To lrinVD
Range("bj" & z).Formula = "=HYPERLINK(""http://www.SomeWebsite.com/catalog/?q=" & Range("B" & z).Value & "&submit=&baseUrl="")"
Next z
[/pre]
-----
 
Var = "AR271WA85RRAINDFAS"

URL = "http://www.SomeWebsite.com/catalog/?q=" + Var + "&submit=&baseUrl="

MsgBox URL
 
URL = "http://www.SomeWebsite.com/catalog/?q=" + Trim(Range("B2").Text) + "&submit=&baseUrl="

MsgBox URL
 
Hi Sir JB-007,


If I need go with loop, then it fine for me.. even I can use .Hyperlink.add

and I dont need LINK.. as finally I need to save the file in CSV..


But when I want to put the same in a bunch.. I failed..

Code: 
Range("bj2:bj" & lrinVD).Formula = "...................."

as you can see I am giving chance to EXCEL to change B2,B3,B4..


any suggestion..


PS: Sorry for .. failure of Span Style tag..

Range("bj2:bj" & lrinVD).Formula = "="http://www.SomeWebsite.com/catalog/?q=" & B2 & "&submit=&baseUrl=""
 
OK, Ninja's,


I am requesting the same question again, in some different way..

Actual URL is

Code: 
http://www.SomeWebsite.com/catalog/?q=AR271WA85RRAINDFAS&submit=&baseUrl=


I need a single line code without loop or any other variable, so that EXCEL will change the formula accordingly.. B2, B3, B4, B5 as in below formula B2 is a relative reference..


[code]Range("c2:c5").Formula = #=CONCATENATE("http://www.SomeWebsite.com/catalog/?q=", B2 ,"&submit=&baseUrl=")#


* I have used # sign just for this post, to display that Formula start and end here..

* I tried to use "&" to tell Excel that this is not ampersand, it is a part of text.. but he is not listening me.. and URL not working..

* If I write

[pre]Range("F2:F5") = "http://www.SomeWebsite.com/catalog/?q="
Range("G2:G5") = "&submit=&baseUrl="
Range("H2:H5").Formula = "= F2 & B2 & G2"[/code][/pre]
It working fine..


Please help to resolve..


Regards,

Deb
 
How's this?

[pre]
Code: 
Range("c2:c5").Formula = "=""http://www.SomeWebsite.com/catalog/?q=""&B2&""&submit=&baseUrl="""
[/pre]
 
Hey Luke..


This is Supeeeeerbbbbbbbbbbb.....

no comments, as it will be the insult of your Ninja TAG...


but how.. and where did you get these extra (") and why EXCEL accept you.. as &B2& is clearly looks that it is a part of TEXT..


Hats off buddy...

May GOD gives you all the happiness and all the access to all the FTP sites..
 
Debraj,


The trick to copy a formula to VBA is:


1. Copy the formula as it is [F2 the cell] from Excel.

2. Paste it in VBE as it is:

i.e.

Range("B2").Formula = MyFormulaWithQuotes

3. Now highlight / select MyFormulaWithQuotes part and then invoke FIND / REPLACE (CTRL + H)

4) Find What: "

Replace With: ""

Do "Selected Text" & Replace All.

5) Now wrap the formula at step 4 with quotes "MyFormulaWithQuotes".


Your formula will work.


The other trick is to use Chr(34) in place of quote.
 
Hi, Debraj Roy!

Arriving at dessert time once more. I see Luke M solved it, well, nothing to do there. Except apologizing for the error of thinking that more clarity would help, that's why I use the For...Next structure.

Regards!
 
As shrivallabja's post alludes to, putting a double quotation mark within a set of quotation marks tells XL that you literally want a single quotation mark.

Example formula to demonstrate, in any cell, put this:

=""""

That's a double quotation mark within a set of quotation marks, so XL displays 1 quotation mark.
 
@ Luke M..

Yes, I have decoded it.. and already praised a lot.. and No more thanks.. because (I hope) we already titled you NINJA.. and we deserve the best answer from YOU.. :)


@ SirJB7 & @Huitson (OK OK.. Narayan you too..)

I think, If in any case NINJA's fail.. then its the fault of questionnaire that he unable to provide requirement correctly..


Its not easy to handle a forum with 20000+ comments and "post-34948" and huge range of multiplicity.. everyday... we are just reader and trying to be a good Follower..


Just for information.. I found some names (sorry.. you know why...) from the below URL in CHANDOO's forum but they are lost in some cloud.. and looks in their BLOG's also, and they are not as interested as it is..


https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Excel&sortby=name


PS: You all have option to EDIT my post wherever you want..
 
@Debraj Roy

Hi!

I've reached that link many times, and each time I do I always exit more scared... Humanity will disappear in less than one generation, if it depended on Excel MVP population... Am I wrong or 95% are men? Or at least not women...

Regards!

PS: What a boring job... Why didn't I play tennis as pro?
 
The tennis world and the sporting public could not deal with two tennis pros with Spanish blood.
 
@b(ut)ob(ut)hc

Hi!

More than Spanish specifically, Rafa is from the very south, Mallorca, I'd say a Mediterranean, while I descend from people of the north, Galizia, I'd say a Celt, you know... :p

Regards!
 
I thought of using VBA for creating the formula in VBA format.


Stay on the cell where formula resides and then run the following code. It will copy the formula in VBA format to clipboard.


Now go to VBA window and do Paste (CTRL + V) and it will paste the adjusted formula with quotes.

[pre]
Code: 
Public Sub CopyExcelFormulaToVBE()
Dim strFormula As String
Dim objDataObj As Object

'Check if we are not on a blank cell!
If Len(ActiveCell.Formula) = 0 Then
MsgBox "No Formula To Copy!", vbCritical
Exit Sub
End If

'Add quotes as required in VBE
strFormula = Chr(34) & Replace(ActiveCell.Formula, Chr(34), Chr(34) & Chr(34)) & Chr(34)

'This is ClsID of MSFORMS Data Object
Set objDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDataObj.SetText strFormula, 1
objDataObj.PutInClipboard
MsgBox "VBA Format formula copied to Clipboard!", vbInformation

Set objDataObj = Nothing

End Sub
[/pre]
 
@shrivallabha

Hi!

You can write this too:

-----

Dim objDataObj As New MSForms.DataObject

objDataObj.SetText strFormula, 1

objDataObj.PutInClipboard

Set objDataObj = Nothing

-----

And so avoid dealing with ClsID.

Regards!
 
SirJB7,


You are right about it. However, it is not considered as good practice in VB as compiler has to switch back and forth to check if the variable has been instantiated or not! I am not too sure if it applies to VBA just the same.


My idea was to write a code using late binding so one doesn't need to reference the MSFORMS library and therefore had to resort to ClsId.


We had had a discussion on MrExcel regarding this:

http://www.mrexcel.com/forum/showthread.php?192500-Syntax-for-late-binding-to-MSFORMS-DataObject/page3
 
@shrivallabha


Hi!


Thanks for the link, I'll go thru it.


The famous early vs. late binding...


Here some additional links:

http://support.microsoft.com/kb/245115

http://msdn.microsoft.com/en-us/library/0tcf61s1.aspx

http://www.excelguru.ca/forums/showthread.php?792-Access-VBA-Writing-to-excel-with-Late-Binding


Check the pros of early in 2nd. one and cons of late in 3rd. However for non-critical performance models, it's just a matter of taste.


My vote to early: shorter, easier, lets me more time for Need For Speed :)


Regards!
 
You must log in or register to reply here.
Back
Top