macro - creating URLs

hey everyone,

i'm trying to create a macro that will put together some URLs for me. i haven't gotten it down - i'm still new at this.

i've got three columns and i have two pieces of code that fit between the three columns.. so it's like this:

columnA + code1 + column B + code2 + column C

the tricky part is that column B and column C may have spaces, which need to be replaced with a + (plus sign). (i used "While Not IsEmpty" for these.)


i'd like to get the new URL to be put into columnE..

my code isn't working - it's my first stab at it. i'm copying it below (and i attached it as a txt file). i'd appreciate any help.. and if you can keep comments in the code then i'll know where i went wrong.


(I would be placing my cursor into column C - which will be the active cell. There are comments beside each step.)

Public Sub URLG()

Dim URL, URL1, URL2, URL3, URL4, newURL1, campaign, newcampaign, character As String
Dim length, counter, place As Integer


campaign = ActiveCell.Value                         'gives me existing campaign name
newcampaign = ActiveCell.Value                      'gives me campaign name in current row
URL4 = "&source=Gg&medium=abc&term="                'part of final URL
URL2 = "&content=A&campaign="                       'part of final URL

While newcampaign = campaign                        'check that the campaign hasn't changed. If not:
    ActiveCell.Offset(0, -2).Select                 'get the front URL with SID
    URL3 = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select                'move to the keyword and pick it up
    URL1 = ActiveCell.Value
        
    While Not IsEmpty(URL1)                         'Run this routine until you have parsed all the of the keyword
    
        newURLl = ""                                'Define the new version of the URL
        length = Len(URLl)                          'get the lngth of keyword - this is the max cycles
        counter = 0                                 'counter counts to length of keyword
        place = 1                                   'my current place/letter position in the keyword
    
        While counter <= length                     'As long as we haven't parsed the entire keyword
            
            character = Mid(URL3, place, 1)         'take the next character in order
            If character = " " Then                 'Set the blank to a + sign
                character = "+"
            End If
    
        newURL1 = newURL1 & character
        counter = counter + 1                       'increment the character count
        place = place + 1                           'move to the next letter in the keyword
    
        Wend
   
URL1 = newURL1

URL = URL3 & URL4 & URL1 & URL2 & campaign
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = URL
ActiveCell.Offset(1, 2).Select
newcampaign = ActiveCell.Value

   
Wend

End Sub

Please wrap your code. See Forum rules below

VBA Noob

sorry.. i wrapped the code in my original post.

last note - the "code1" and "code2" will be the same throughout the whole URL generation - only the values in columns A - C are changing.