Hi, I have an automated report (which we cannot change ) that is not
applying data constantly, event if they are in the right order. What I need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would like
to copy Montreal and paste it up to cell A4 then start the same process with
Quebec. The number of time that Montreal (in the example could be paste can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
....


--
Mathieu

Hi,

It's either a helper column or a macro. using a helper column enter Montreal
in B1 then the formula below in B2 and drag down as far as required. If you
need a macro post back.

=IF(A2="",B1,A2)

Paste the new column over the original with paste special - values and then
delete the helper column
Mike

"Mathieu" wrote:

> Hi, I have an automated report (which we cannot change ) that is not
> applying data constantly, event if they are in the right order. What I need
> to do is to Copy the first line and paste it until the next value. I am
> really stuck on this one. Thanks for your help. On the exemple. I would like
> to copy Montreal and paste it up to cell A4 then start the same process with
> Quebec. The number of time that Montreal (in the example could be paste can
> vary a lot from time to time and all the other fields below have the same
> problem.
>
>
> A B C
> 1 Montreal
> 2
> 3
> 4
> 5 Quebec
> 6
> 7
> 8
> ...
>
>
> --
> Mathieu

Hi

Try this:

Sub FillIn()
MyString = Range("A1").Value
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To LastRow
If Cells(r, 1) = "" Then
Cells(r, 1) = MyString
Else
MyString = Cells(r, 1).Value
End If
Next
End Sub

Regards,
Per

"Mathieu" <(E-Mail Removed)> skrev i meddelelsen
news:98D3DA00-3DBE-4448-9D29-(E-Mail Removed)...
> Hi, I have an automated report (which we cannot change ) that is not
> applying data constantly, event if they are in the right order. What I
> need
> to do is to Copy the first line and paste it until the next value. I am
> really stuck on this one. Thanks for your help. On the exemple. I would
> like
> to copy Montreal and paste it up to cell A4 then start the same process
> with
> Quebec. The number of time that Montreal (in the example could be paste
> can
> vary a lot from time to time and all the other fields below have the same
> problem.
>
>
> A B C
> 1 Montreal
> 2
> 3
> 4
> 5 Quebec
> 6
> 7
> 8
> ...
>
>
> --
> Mathieu

Hi,

It's need to be a macro. They can be hundreds of information to paste. I am
using town from Canada in my exemple. Let's assume we will use EVERY cities
in Canada. This is why a macro is required. I considered myself a
beginner-novice with VBA, I understand the basis for the moment but this one
is completely eluding me.

Thanks
--
Mathieu


"Mike H" wrote:

> Hi,
>
> It's either a helper column or a macro. using a helper column enter Montreal
> in B1 then the formula below in B2 and drag down as far as required. If you
> need a macro post back.
>
> =IF(A2="",B1,A2)
>
> Paste the new column over the original with paste special - values and then
> delete the helper column
> Mike
>
> "Mathieu" wrote:
>
> > Hi, I have an automated report (which we cannot change ) that is not
> > applying data constantly, event if they are in the right order. What I need
> > to do is to Copy the first line and paste it until the next value. I am
> > really stuck on this one. Thanks for your help. On the exemple. I would like
> > to copy Montreal and paste it up to cell A4 then start the same process with
> > Quebec. The number of time that Montreal (in the example could be paste can
> > vary a lot from time to time and all the other fields below have the same
> > problem.
> >
> >
> > A B C
> > 1 Montreal
> > 2
> > 3
> > 4
> > 5 Quebec
> > 6
> > 7
> > 8
> > ...
> >
> >
> > --
> > Mathieu

I anticipated that and have one written. this fills Column A as fra as there
are dat in Column B

Right click your sheet tab, view code and paste it in and run it.

Sub Canadian_Fill()
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow - 1)
For Each c In MyRange
If c.Offset(1).Value = "" Then
c.Offset(1).Value = c.Value
End If
Next
End Sub

Mike

"Mathieu" wrote:

> Hi,
>
> It's need to be a macro. They can be hundreds of information to paste. I am
> using town from Canada in my exemple. Let's assume we will use EVERY cities
> in Canada. This is why a macro is required. I considered myself a
> beginner-novice with VBA, I understand the basis for the moment but this one
> is completely eluding me.
>
> Thanks
> --
> Mathieu
>
>
> "Mike H" wrote:
>
> > Hi,
> >
> > It's either a helper column or a macro. using a helper column enter Montreal
> > in B1 then the formula below in B2 and drag down as far as required. If you
> > need a macro post back.
> >
> > =IF(A2="",B1,A2)
> >
> > Paste the new column over the original with paste special - values and then
> > delete the helper column
> > Mike
> >
> > "Mathieu" wrote:
> >
> > > Hi, I have an automated report (which we cannot change ) that is not
> > > applying data constantly, event if they are in the right order. What I need
> > > to do is to Copy the first line and paste it until the next value. I am
> > > really stuck on this one. Thanks for your help. On the exemple. I would like
> > > to copy Montreal and paste it up to cell A4 then start the same process with
> > > Quebec. The number of time that Montreal (in the example could be paste can
> > > vary a lot from time to time and all the other fields below have the same
> > > problem.
> > >
> > >
> > > A B C
> > > 1 Montreal
> > > 2
> > > 3
> > > 4
> > > 5 Quebec
> > > 6
> > > 7
> > > 8
> > > ...
> > >
> > >
> > > --
> > > Mathieu

Thank you, it is working great! I will now try to understand the difference
with yours and Per Jessen. Both are working Great!

Thanks again!
--
Mathieu


"Mike H" wrote:

> I anticipated that and have one written. this fills Column A as fra as there
> are dat in Column B
>
> Right click your sheet tab, view code and paste it in and run it.
>
> Sub Canadian_Fill()
> LastRow = Cells(Rows.Count, "B").End(xlUp).Row
> Set MyRange = Range("A1:A" & LastRow - 1)
> For Each c In MyRange
> If c.Offset(1).Value = "" Then
> c.Offset(1).Value = c.Value
> End If
> Next
> End Sub
>
> Mike
>
> "Mathieu" wrote:
>
> > Hi,
> >
> > It's need to be a macro. They can be hundreds of information to paste. I am
> > using town from Canada in my exemple. Let's assume we will use EVERY cities
> > in Canada. This is why a macro is required. I considered myself a
> > beginner-novice with VBA, I understand the basis for the moment but this one
> > is completely eluding me.
> >
> > Thanks
> > --
> > Mathieu
> >
> >
> > "Mike H" wrote:
> >
> > > Hi,
> > >
> > > It's either a helper column or a macro. using a helper column enter Montreal
> > > in B1 then the formula below in B2 and drag down as far as required. If you
> > > need a macro post back.
> > >
> > > =IF(A2="",B1,A2)
> > >
> > > Paste the new column over the original with paste special - values and then
> > > delete the helper column
> > > Mike
> > >
> > > "Mathieu" wrote:
> > >
> > > > Hi, I have an automated report (which we cannot change ) that is not
> > > > applying data constantly, event if they are in the right order. What I need
> > > > to do is to Copy the first line and paste it until the next value. I am
> > > > really stuck on this one. Thanks for your help. On the exemple. I would like
> > > > to copy Montreal and paste it up to cell A4 then start the same process with
> > > > Quebec. The number of time that Montreal (in the example could be paste can
> > > > vary a lot from time to time and all the other fields below have the same
> > > > problem.
> > > >
> > > >
> > > > A B C
> > > > 1 Montreal
> > > > 2
> > > > 3
> > > > 4
> > > > 5 Quebec
> > > > 6
> > > > 7
> > > > 8
> > > > ...
> > > >
> > > >
> > > > --
> > > > Mathieu

Thank you, it is working great! I will now try to understand the difference
with yours and Mike H. Both are working Great!

Thanks again!
--
Mathieu


"Per Jessen" wrote:

> Hi
>
> Try this:
>
> Sub FillIn()
> MyString = Range("A1").Value
> LastRow = Range("A" & Rows.Count).End(xlUp).Row
> For r = 2 To LastRow
> If Cells(r, 1) = "" Then
> Cells(r, 1) = MyString
> Else
> MyString = Cells(r, 1).Value
> End If
> Next
> End Sub
>
> Regards,
> Per
>
> "Mathieu" <(E-Mail Removed)> skrev i meddelelsen
> news:98D3DA00-3DBE-4448-9D29-(E-Mail Removed)...
> > Hi, I have an automated report (which we cannot change ) that is not
> > applying data constantly, event if they are in the right order. What I
> > need
> > to do is to Copy the first line and paste it until the next value. I am
> > really stuck on this one. Thanks for your help. On the exemple. I would
> > like
> > to copy Montreal and paste it up to cell A4 then start the same process
> > with
> > Quebec. The number of time that Montreal (in the example could be paste
> > can
> > vary a lot from time to time and all the other fields below have the same
> > problem.
> >
> >
> > A B C
> > 1 Montreal
> > 2
> > 3
> > 4
> > 5 Quebec
> > 6
> > 7
> > 8
> > ...
> >
> >
> > --
> > Mathieu
>
>