Loops & Ifs

Shorty · 03-03-2006, 07:25 AM

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

Shorty · 03-03-2006, 07:30 AM

Sorry, the table screwed up on posting, ive put a picture of the table
here
http://ashort.home.cern.ch/ashort/data.gif

Bob Phillips · 03-03-2006, 08:20 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Shorty · 03-03-2006, 08:25 PM

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