Excel VBA Looping Actions

[crowso]

Hi,

I'm trying to repeat an action which I think requires a "Do Until" loop but I'm not entirely sure if it does and I have no idea how to loop! So any help would be greatly appreciated!


My current code is:

Sub Quarter()
'
' Macro1 Macro
'
'
If Range("A1") > 0 Then
Sheets("Flights").Select
Range("Revenue").Select
Selection.Copy
Sheets("Rows Linked to Flights").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If Range("A1").Value = "" Then Range("B1") = ""
End If
If Range("A2") > 0 Then
Sheets("Flights").Select
Range("Revenue").Select
Selection.Copy
Sheets("Rows Linked to Flights").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If Range("A2").Value = "" Then Range("B2") = ""
End If
If Range("A3") > 0 Then
Sheets("Flights").Select
Range("Revenue").Select
Selection.Copy
Sheets("Rows Linked to Flights").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If Range("A3").Value = "" Then Range("B3") = ""
End If


I want it to go down to row 200 but unsure how to go about it?

Any help would be greatly appreciated!

 

[Rick Rothstein]

What is the range for the Defined Name "Revenue"?

 

[crowso]

Thanks for the quick response!

The defined name "Revenue" changes value each time, but the range is roughly from 30,000 to 600,000

 

[crowso]

Sorry I should clarify that the defined name "Revenue" is one cell but the values within that one cell changes from about £30,000 to £600,000.

 

[henrik2h]

No expert but something along these lines

Sub Copyflightdata()
Dim vbRevenue As Long


vbRevenue = Sheets("Flights").Range("Revenue")
nRows = Sheets("Flights").Range("A1:A200").Count


For i = 1 To nRows
If Sheets("Flights").Range("A" & i) > 0 Then
Sheets("Row Linked to Flights").Range("B" & i) = vbRevenue
End If
Next i


End Sub

 

[crowso]

Unfortunately it hasn't worked, it's putting revenue in random places down column B. I appreciate the help though

 

[henrik2h]

It is putting values in same row as it got the data (as in your example). If you want it pasted after each other in the new sheet you need a second loop.

Sub Copyflightdata()Dim vbRevenue As Long, i As Integer, j As Integer




vbRevenue = Sheets("Flights").Range("Revenue")
nRows = Sheets("Flights").Range("A1:A200").Count




For i = 1 To nRows
If Sheets("Flights").Range("A" & i) > 0 Then
j = j + 1
Sheets("Row Linked to Flights").Range("B" & j) = vbRevenue
End If
Next i




End Sub

 

[crowso]

I tried it but for some reason went down to row 27 but no further or less?

I've tried this code but having some issues with it. I can't seem to control when it stops!

Any idea what I've done wrong?


Sub Looping()
'
'
Dim i As Long
For i = 1 To 200
If Range("A" & i).Value > 0 Then
Sheets("Flights").Select
Range("Revenue").Select
Selection.Copy
Sheets("Rows Linked to Flights").Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next i
End Sub