Hiya,
I have a spreadsheet (sheet1) containing 26 columns and 1000+ rows of data. Column A is date, the other 25 are bird species, and the cells relate to how many birds were seen on that day.
But what i want to do is a little complex:
A second spreadsheet has 45 columns of data, the first one is date ,and the next 25 columns relate to the 25 in sheet1, the next 19 are birstrike information (ie when and how the bird hit the plane)
If the birdstrike occurred in column 3 (crow) then there would be a 1 in that cell, but 0's in all the other cells of that row.
with me?
oh it gets worse...
I want to make a macro that searches along the rows in sheet2, columns 2:26, and when until it finds a 1 it needs to go back to column A, match the date with column A in sheet 1 and pull out the data in the corresponding cell in sheet1 into column 46 in sheet 2.
here's my attempt, very messy and long, but it was the only way i could get close (after a days fiddling)
Sub Strikeupdate3()
For A = 2 To 5000
If Worksheets("Strikes2").Cells(A, 1) = "" Then
Exit Sub
End If
If Worksheets("strikes2").Cells(A, 1) = Worksheets("Data").Cells(A, 1) Then
GoTo Line2
End If
Line2:
If Worksheets("strikes2").Cells(A, 2) = 1 Then
GoTo Line3
Else: GoTo Line28
End If
Line28:
If Worksheets("strikes2").Cells(A, 3) = "1" Then
GoTo Line4
Else: GoTo Line29
End If
Line29:
If Worksheets("strikes2").Cells(A, 4) = "1" Then
GoTo Line5
Else: GoTo Line30
End If
Line30:
If Worksheets("strikes2").Cells(A, 5) = "1" Then
GoTo Line6
Else: GoTo Line31
End If
Line31:
If Worksheets("strikes2").Cells(A, 6) = "1" Then
GoTo Line7
Else: GoTo Line32
End If
Line32:
If Worksheets("strikes2").Cells(A, 7) = "1" Then
GoTo Line8
Else: GoTo Line33
End If
Line33:
If Worksheets("strikes2").Cells(A, = "1" Then
GoTo Line9
Else: GoTo Line34
End If
Line34:
If Worksheets("strikes2").Cells(A, 9) = "1" Then
GoTo Line10
Else: GoTo Line35
End If
Line35:
If Worksheets("strikes2").Cells(A, 10) = "1" Then
GoTo Line11
Else: GoTo Line36
End If
Line36:
If Worksheets("strikes2").Cells(A, 11) = "1" Then
GoTo Line12
Else: GoTo Line37
End If
Line37:
If Worksheets("strikes2").Cells(A, 12) = "1" Then
GoTo Line13
Else: GoTo Line38
End If
Line38:
If Worksheets("strikes2").Cells(A, 13) = "1" Then
GoTo Line14
Else: GoTo Line39
End If
Line39:
If Worksheets("strikes2").Cells(A, 14) = "1" Then
GoTo Line15
Else: GoTo Line40
End If
Line40:
If Worksheets("strikes2").Cells(A, 15) = "1" Then
GoTo Line16
Else: GoTo Line41
End If
Line41:
If Worksheets("strikes2").Cells(A, 16) = "1" Then
GoTo Line17
Else: GoTo Line42
End If
Line42:
If Worksheets("strikes2").Cells(A, 17) = "1" Then
GoTo Line18
Else: GoTo Line43
End If
Line43:
If Worksheets("strikes2").Cells(A, 18) = "1" Then
GoTo Line19
Else: GoTo Line44
End If
Line44:
If Worksheets("strikes2").Cells(A, 19) = "1" Then
GoTo Line20
Else: GoTo Line45
End If
Line45:
If Worksheets("strikes2").Cells(A, 20) = "1" Then
GoTo Line21
Else: GoTo Line46
End If
Line46:
If Worksheets("strikes2").Cells(A, 21) = "1" Then
GoTo Line22
Else: GoTo Line47
End If
Line47:
If Worksheets("strikes2").Cells(A, 22) = "1" Then
GoTo Line23
Else: GoTo Line48
End If
Line48:
If Worksheets("strikes2").Cells(A, 23) = "1" Then
GoTo Line24
Else: GoTo Line49
End If
Line49:
If Worksheets("strikes2").Cells(A, 24) = "1" Then
GoTo Line25
Else: GoTo Line50
End If
Line50:
If Worksheets("strikes2").Cells(A, 25) = "1" Then
GoTo Line26
Else: GoTo Line51
End If
Line51:
If Worksheets("strikes2").Cells(A, 26) = "1" Then
GoTo Line27
End If
Line3:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 2)
Line4:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 3)
Line5:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 4)
Line6:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 5)
Line7:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 6)
Line8:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 7)
Line9:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A,
Line10:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 9)
Line11:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 10)
Line12:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 11)
Line13:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 12)
Line14:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 13)
Line15:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 14)
Line16:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 15)
Line17:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 16)
Line18:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 17)
Line19:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 18)
Line20:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 19)
Line21:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 20)
Line22:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 21)
Line23:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 22)
Line24:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 23)
Line25:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 24)
Line26:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 25)
Line27:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 26)
Next
End Sub
I have a spreadsheet (sheet1) containing 26 columns and 1000+ rows of data. Column A is date, the other 25 are bird species, and the cells relate to how many birds were seen on that day.
But what i want to do is a little complex:
A second spreadsheet has 45 columns of data, the first one is date ,and the next 25 columns relate to the 25 in sheet1, the next 19 are birstrike information (ie when and how the bird hit the plane)
If the birdstrike occurred in column 3 (crow) then there would be a 1 in that cell, but 0's in all the other cells of that row.
with me?
oh it gets worse...
I want to make a macro that searches along the rows in sheet2, columns 2:26, and when until it finds a 1 it needs to go back to column A, match the date with column A in sheet 1 and pull out the data in the corresponding cell in sheet1 into column 46 in sheet 2.
here's my attempt, very messy and long, but it was the only way i could get close (after a days fiddling)
Sub Strikeupdate3()
For A = 2 To 5000
If Worksheets("Strikes2").Cells(A, 1) = "" Then
Exit Sub
End If
If Worksheets("strikes2").Cells(A, 1) = Worksheets("Data").Cells(A, 1) Then
GoTo Line2
End If
Line2:
If Worksheets("strikes2").Cells(A, 2) = 1 Then
GoTo Line3
Else: GoTo Line28
End If
Line28:
If Worksheets("strikes2").Cells(A, 3) = "1" Then
GoTo Line4
Else: GoTo Line29
End If
Line29:
If Worksheets("strikes2").Cells(A, 4) = "1" Then
GoTo Line5
Else: GoTo Line30
End If
Line30:
If Worksheets("strikes2").Cells(A, 5) = "1" Then
GoTo Line6
Else: GoTo Line31
End If
Line31:
If Worksheets("strikes2").Cells(A, 6) = "1" Then
GoTo Line7
Else: GoTo Line32
End If
Line32:
If Worksheets("strikes2").Cells(A, 7) = "1" Then
GoTo Line8
Else: GoTo Line33
End If
Line33:
If Worksheets("strikes2").Cells(A, = "1" Then
GoTo Line9
Else: GoTo Line34
End If
Line34:
If Worksheets("strikes2").Cells(A, 9) = "1" Then
GoTo Line10
Else: GoTo Line35
End If
Line35:
If Worksheets("strikes2").Cells(A, 10) = "1" Then
GoTo Line11
Else: GoTo Line36
End If
Line36:
If Worksheets("strikes2").Cells(A, 11) = "1" Then
GoTo Line12
Else: GoTo Line37
End If
Line37:
If Worksheets("strikes2").Cells(A, 12) = "1" Then
GoTo Line13
Else: GoTo Line38
End If
Line38:
If Worksheets("strikes2").Cells(A, 13) = "1" Then
GoTo Line14
Else: GoTo Line39
End If
Line39:
If Worksheets("strikes2").Cells(A, 14) = "1" Then
GoTo Line15
Else: GoTo Line40
End If
Line40:
If Worksheets("strikes2").Cells(A, 15) = "1" Then
GoTo Line16
Else: GoTo Line41
End If
Line41:
If Worksheets("strikes2").Cells(A, 16) = "1" Then
GoTo Line17
Else: GoTo Line42
End If
Line42:
If Worksheets("strikes2").Cells(A, 17) = "1" Then
GoTo Line18
Else: GoTo Line43
End If
Line43:
If Worksheets("strikes2").Cells(A, 18) = "1" Then
GoTo Line19
Else: GoTo Line44
End If
Line44:
If Worksheets("strikes2").Cells(A, 19) = "1" Then
GoTo Line20
Else: GoTo Line45
End If
Line45:
If Worksheets("strikes2").Cells(A, 20) = "1" Then
GoTo Line21
Else: GoTo Line46
End If
Line46:
If Worksheets("strikes2").Cells(A, 21) = "1" Then
GoTo Line22
Else: GoTo Line47
End If
Line47:
If Worksheets("strikes2").Cells(A, 22) = "1" Then
GoTo Line23
Else: GoTo Line48
End If
Line48:
If Worksheets("strikes2").Cells(A, 23) = "1" Then
GoTo Line24
Else: GoTo Line49
End If
Line49:
If Worksheets("strikes2").Cells(A, 24) = "1" Then
GoTo Line25
Else: GoTo Line50
End If
Line50:
If Worksheets("strikes2").Cells(A, 25) = "1" Then
GoTo Line26
Else: GoTo Line51
End If
Line51:
If Worksheets("strikes2").Cells(A, 26) = "1" Then
GoTo Line27
End If
Line3:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 2)
Line4:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 3)
Line5:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 4)
Line6:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 5)
Line7:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 6)
Line8:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 7)
Line9:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A,
Line10:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 9)
Line11:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 10)
Line12:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 11)
Line13:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 12)
Line14:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 13)
Line15:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 14)
Line16:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 15)
Line17:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 16)
Line18:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 17)
Line19:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 18)
Line20:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 19)
Line21:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 20)
Line22:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 21)
Line23:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 22)
Line24:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 23)
Line25:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 24)
Line26:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 25)
Line27:
Worksheets("strikes2").Cells(A, 42) = Worksheets("Data").Cells(A, 26)
Next
End Sub