Need to create URL variations

[Sharid]

Hi

I have been stuck on this one for sometime now and need so help, please. I need a code that will create variations of urls.

In Sheet 1 I will have a list of urls, column A row 2 down. Some urls may NOT end with a forward slash, the urls will also end with different prefix .com .co.uk .org and so on.

In Sheet2 I will have the variations I need it to create of the url.

When the command button is clicked I need the following to be created, in Sheet1 Column A Row 2 Down. The red colour is only for your reference its is not necessary.

I am not sure if it is possible to delete everything after the prefix e.g. .com .co.uk and so on in the url BEFORE it created the variation of the url.

I can not seem to work it out. As always thanks in advance.

 

[FormR]

Hi, here is some code you can try.

Sub m()
Dim a As Variant, b As Variant, c() As String, i As Long, j As Long, k As Long

With Sheets("Sheet1")
    a = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With

With Sheets("Sheet2")
    b = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With

ReDim c(1 To UBound(a) * UBound(b))

For i = 1 To UBound(a)
    For j = 1 To UBound(b)
        k = k + 1
        c(k) = a(i, 1) & IIf(Right(a(i, 1), 1) = "\", "", "\") & b(j, 1)
    Next j
Next i

Sheets("Sheet1").Range("A2").Resize(k).Value = Application.Transpose(c)

End Sub

 

[Sharid]

FormR

This is super so far and the first half works super.

Results

Just does not do this bit "I am not sure if it is possible to delete everything after the prefix e.g. .com .co.uk and so on in the url BEFORE it created the variation of the url. "

Starting URLS

Results - The red results are wrong, they should look like the green results.

Not too sure how this would be done as urls will be different sizes and will have different prefix .org .com. .uk .co.uk .biz and so on

 

[FormR]

Not too sure how this would be done as urls will be different sizes and will have different prefix .org .com. .uk .co.uk .biz and so on

Hi, I think you'd need a definitive list of possible domain names to check against.

 

[Sharid]

I'm not to sure what you mean by "definitive" are you stating an actual url list as in my example i am using made up urls OR are you stating "definitive" as an actual FORMAT of urls e.g. AFTER the prefix .com .co.uk and so on there can be no further text so the code works.

Example Actual URLS
Start - These are actual urls and the results are shown below.

Results - All these result are wrong

They should look like this

URL FORMAT
Or do you mean that the urls need to be cleaned and have a SET FORMAT SO
These URLS

NEED to look like this before the code is run

As I was hoping a code would do this before it creates the variations. See Below

 

[FormR]

Hi, I mean a list of domain names i.e
.com
.co.uk
.cn
etc..

As an aside, you should consider installing the XL2BB add-in to post your example data instead of posting images as it allows your potential helpers to copy your data into Excel instead of having to manually re-type it (which can put many people of helping).

 

[Sharid]

Thanks for the up date. I was hoping a code could format the urls but there are too many variations to domains, never mind. i will make do as is thanks for your support.

ps - just done it as advised

 

[FormR]

Hi, I've had a bit more time this morning to digest this, I think this does what you want.

Sub m()
Dim a As Variant, b As Variant, c() As String, i As Long, j As Long, k As Long

With Sheets("Sheet1")
    a = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With

With Sheets("Sheet2")
    b = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With

ReDim c(1 To UBound(a) * UBound(b))

For i = 1 To UBound(a)
    For j = 1 To UBound(b)
        k = k + 1
        c(k) = Left(a(i, 1) & "\", InStr(9, a(i, 1) & "\", "\")) & b(j, 1)
    Next j
Next i

Sheets("Sheet1").Range("A2").Resize(k).Value = Application.Transpose(c)

End Sub

 

[Sharid]

Super, this is exactly what I was after.

Thanks FormR
There was however one slight problem with your code, luckly it was within my programing skills to fix this major problem. I changed the BackSlash "\" to a ForwardSlash "/" ?????. Its all good now " Some urls may NOT end with a forward slash, "

 

[FormR]

I changed the BackSlash "\" to a ForwardSlash "/"

Oops, yes - well spotted