Urgent - Function needed to done this

[Abbas999]

Hi,

I have problem Please look at the picture below:

In this picture you can each url ending with pagenum=1

There are 29670 rows in excel sheet and all ending with pagenum=1

And also you can see the zip code which is "Ab10" for all 29670 rows.

WHAT I AM LOOKING FOR A FUNCTION THAT WILL REPLACE EACH ZIP CODE AFTER 10 ROWS AND add PAGENUM=1-10

For example First zip code is AB10 so i want first 10 rows like this and then next zip code is "ab11" and so on for all zip codes. Check image below for better understanding.

So the gap is 10 rows. After each 10 rows the ab10 should be replaced with next zip code and the pagenum should add 10 pages.
E.g
Pagenum=1
Pagenum=2
Pagenum=3
and so on.. for each 10 rows.

I have a list of zip codes already. Please Help me if it is possible to do. Thank you in advance

 

[Rick Rothstein]

Here is a macro that you can try...

Sub FixData()
  Dim X As Long, Z As Long, LastRow As Long, Template As String, CellText As String
  Dim ZipNumStart As Long, ZipEnd As Long, ZipNum As Long, Result As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  ReDim Result(1 To LastRow, 1 To 1)
  CellText = Range("A1").Value
  Template = Left(CellText, Len(CellText) - 1)
  For X = InStr(Template, "&location=") + 10 To Len(Template)
    If Mid(Template, X, 1) Like "#" Then
      ZipNumStart = X
      Exit For
    End If
  Next
  For X = ZipNumStart To Len(Template)
    If Mid(Template, X, 1) Like "[!0-9]" Then
      ZipNum = Mid(Template, ZipNumStart, X - ZipNumStart)
      ZipEnd = X - 1
      Exit For
    End If
  Next
  For X = 1 To UBound(Result) - 1 Step 10
    CellText = Left(Template, ZipNumStart - 1) & (ZipNum + Int((X - 1) / 10)) & Mid(Template, ZipEnd + 1)
    On Error Resume Next
    For Z = 1 To 10
      CellText = Left(CellText, Len(Template)) & Z
      If Err.Number Then Exit For
      Result(X + Z - 1, 1) = CellText
    Next
    On Error GoTo 0
  Next
  Range("A1").Resize(UBound(Result)) = Result
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (FixData) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Abbas999]

@Konew1 Thank you so much, I tried many times but failed at final try i was succeed. You saved my alot of time. THank you very much.

 

[Mich521]

konew1's solution works.

Your workbook should have one sheet named zip codes. In it, write all of your zip codes in column A

A	B
AB10
AB11
AB12
AB13

<tbody>
</tbody>

Then enter konew1's code into sheets 2 and 3 exactly as illustrated in the previous post. All you have to do after that on Sheet 2 is fill down until you have generated your desired number of urls.

 

[konew1]

Great. pleased to help