Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops & Ifs
Hello,
I have made a worksheet to download the data shown below. Now I am faced with what I can only see as the almost impossible task of creating a macro, which will go through the data. For each row, from column E to P I want to insert a URL on the same row starting from column Y. if the cell = abnd I don't want to insert the URL and if the cell is empty I don't want to insert a URL. Here is the data im getting; ROW C D E F G H I J K L M N O P Count 1 2 3 4 5 6 7 8 9 10 11 12 12 BR 8 Abnd Abnd Abnd 7,1,10 11,3,5 5,1,4 F4 Abnd Abnd 13 MR 7 3,1,6 6,4,9 7,1,10 6,1,4 7,F4 11,3,5 3,2,7 14 NR 5 2,8,9 5,1,9 2,9,4 2,4,5 2,8,4 15 VR 5 2,5,3 8,6,9 2,4,8 5,4,3 5,1,4 F4 16 CR 7 14,13,10 1,2,3 6,11,7 9,3,1 12,3,5 5,1,4 11,5,3 17 Here is the code I have somehow managed to make!! I think its overly complicated but it works! Unless there is a gap in the data it still creates a URL AND if the cell is Abnd it still creates the URL. I don't know where to insert the code to stop this!! Sub CalculateURLs() Dim columnI As Integer Dim rowI As Integer Dim numberOfRacesI As Integer Dim numberOfRaces As Integer Dim raceCode As String Dim todaysDate As String Dim lastRaceURL As String Dim startRaceCount As Integer todaysDate = Range("A2") Sheets("Imported Data").Select rowI = 9 columnI = 5 startRaceCount = 1 Do Range("C" & rowI).Select If ActiveCell = IsEmpty("C" & rowI) Then rowI = rowI + 1 Else Range("D" & rowI).Select If ActiveCell = IsEmpty("D" & rowI) Then If ActiveCell = Range("S" & rowI) Then rowI = rowI + 1 Else End If Else raceCode = Range("C" & rowI) numberOfRaces = Range("D" & rowI) numberOfRacesI = 1 startRaceCount = 1 ActiveCell.Offset(0, 1).Select If ActiveCell = "" Then Do ActiveCell.Offset(0, 1).Select columnI = columnI + 1 startRaceCount = startRaceCount + 1 Loop While ActiveCell = "" End If If ActiveCell = "" Then Else Range("Y" & rowI).Select columnI = 25 Do If startRaceCount <= 9 Then lastRaceURL = "URL;http://www.getdata.com/" & todaysDate & "/" & raceCode & "0" & startRaceCount & ".html" Else lastRaceURL = "URL;http://www.getdata.com/" & todaysDate & "/" & raceCode & startRaceCount & ".html" End If ActiveCell = lastRaceURL startRaceCount = startRaceCount + 1 numberOfRacesI = numberOfRacesI + 1 columnI = columnI + 1 ActiveSheet.Cells(rowI, columnI).Select Loop Until numberOfRacesI numberOfRaces rowI = rowI + 1 columnI = 25 startRaceCount = 0 End If End If End If Range("A" & rowI + 1).Select Loop Until ActiveCell = "Last Race Results" End Sub Any help would be greatly appreciated! Sorry I cant explain it any better than this, just give me a shout if you need any more info! Thanks, Shorty |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops & Ifs
Sorry, the table screwed up on posting, ive put a picture of the table
here http://ashort.home.cern.ch/ashort/data.gif |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops & Ifs
Shorty,
Try this Sub CalculateURLs() Const kURL As String = "URL;http://www.getdata.com/" Dim iLastRow As Long Dim iLastcol As Long Dim sToday As String Dim sRaceCode As String With Sheets("Imported Data") sToday = .Range("A2").Text iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For i = 9 To iLastRow If .Cells(i, "C").Value < "" Then sRaceCode = .Cells(i, "C").Value iLastcol = .Cells(i, .Columns.Count).End(xlToLeft).Column For j = 5 To iLastcol If .Cells(i, j).Value < "" Then lastRaceURL = kURL & sToday & "/" & sRaceCode & _ Format(j - 4, "00") & ".html" End If Next j End If Next i End With End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Shorty" wrote in message oups.com... Hello, I have made a worksheet to download the data shown below. Now I am faced with what I can only see as the almost impossible task of creating a macro, which will go through the data. For each row, from column E to P I want to insert a URL on the same row starting from column Y. if the cell = abnd I don't want to insert the URL and if the cell is empty I don't want to insert a URL. Here is the data im getting; ROW C D E F G H I J K L M N O P Count 1 2 3 4 5 6 7 8 9 10 11 12 12 BR 8 Abnd Abnd Abnd 7,1,10 11,3,5 5,1,4 F4 Abnd Abnd 13 MR 7 3,1,6 6,4,9 7,1,10 6,1,4 7,F4 11,3,5 3,2,7 14 NR 5 2,8,9 5,1,9 2,9,4 2,4,5 2,8,4 15 VR 5 2,5,3 8,6,9 2,4,8 5,4,3 5,1,4 F4 16 CR 7 14,13,10 1,2,3 6,11,7 9,3,1 12,3,5 5,1,4 11,5,3 17 Here is the code I have somehow managed to make!! I think its overly complicated but it works! Unless there is a gap in the data it still creates a URL AND if the cell is Abnd it still creates the URL. I don't know where to insert the code to stop this!! Sub CalculateURLs() Dim columnI As Integer Dim rowI As Integer Dim numberOfRacesI As Integer Dim numberOfRaces As Integer Dim raceCode As String Dim todaysDate As String Dim lastRaceURL As String Dim startRaceCount As Integer todaysDate = Range("A2") Sheets("Imported Data").Select rowI = 9 columnI = 5 startRaceCount = 1 Do Range("C" & rowI).Select If ActiveCell = IsEmpty("C" & rowI) Then rowI = rowI + 1 Else Range("D" & rowI).Select If ActiveCell = IsEmpty("D" & rowI) Then If ActiveCell = Range("S" & rowI) Then rowI = rowI + 1 Else End If Else raceCode = Range("C" & rowI) numberOfRaces = Range("D" & rowI) numberOfRacesI = 1 startRaceCount = 1 ActiveCell.Offset(0, 1).Select If ActiveCell = "" Then Do ActiveCell.Offset(0, 1).Select columnI = columnI + 1 startRaceCount = startRaceCount + 1 Loop While ActiveCell = "" End If If ActiveCell = "" Then Else Range("Y" & rowI).Select columnI = 25 Do If startRaceCount <= 9 Then lastRaceURL = "URL;http://www.getdata.com/" & todaysDate & "/" & raceCode & "0" & startRaceCount & ".html" Else lastRaceURL = "URL;http://www.getdata.com/" & todaysDate & "/" & raceCode & startRaceCount & ".html" End If ActiveCell = lastRaceURL startRaceCount = startRaceCount + 1 numberOfRacesI = numberOfRacesI + 1 columnI = columnI + 1 ActiveSheet.Cells(rowI, columnI).Select Loop Until numberOfRacesI numberOfRaces rowI = rowI + 1 columnI = 25 startRaceCount = 0 End If End If End If Range("A" & rowI + 1).Select Loop Until ActiveCell = "Last Race Results" End Sub Any help would be greatly appreciated! Sorry I cant explain it any better than this, just give me a shout if you need any more info! Thanks, Shorty |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops & Ifs
Thats amazing! thank you! less than half the code i had!
Im still stuck on how to insert the "lastRaceURL" into the sheet starting column Y and leaving no empty cells between race urls. I have tried to enter Range("Y" & iLastRow).Select which takes me to the last row used (hence the variable name! ;-) ) is there a simple way round this to generate the urls like below? starting in column Y and which ever row the first race is on? URL;http://www.getdata.com/2006/03/02/QR01.html URL;http://www.getdata.com/2006/03/02/QR02.html URL;http://www.getdata.com/2006/03/02/QR03.html URL;http://www.getdata.com/2006/03/02/NR03.html URL;http://www.getdata.com/2006/03/02/NR04.html URL;http://www.getdata.com/2006/03/02/VR02.html URL;http://www.getdata.com/2006/03/02/VR05.html Thanks again, Shorty |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loops & Ifs
oh ive got it sussed now, F8 helped me add a loop into the code! Thanks
again for help!! Shorty |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops | Excel Programming | |||
Do loops within Do loops | Excel Programming | |||
Using For - Next Loops in VB | New Users to Excel | |||
Loops | Excel Programming | |||
help with loops | Excel Programming |