VBA autofill until a value change on an adjacent column

[Danilo Montoni]

Im new to VBA so i would very much appreciate any help. I want to autofill a formula until a value change on an adjacent column. example:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">___A_____B_________

1 CAR =formula
2 CAR
3 CAR
4 GLO
5 GLO
6 GLO
</code>
In the example above: Autofill the formula from b1 to b3 (when the value on column A changes from CAR to GLO). PS: The number of rows with the imput CAR and GLO varies from month to month. Thanks!!!

 

[hateme28]

what do you mean autofill the formula? what would you like in cells b1 through b3?

 

[dura1]

Try this:

Sub vzorec()
radek = 1 'number of row where could start formulas
Do Until Cells(radek, 1).Value <> "CAR"
Cells(radek, 2).Formula = "=LEN(RC[-1])" 'the formula is up to you
radek = radek + 1
Loop


End Sub

 

[Danilo Montoni]

what do you mean autofill the formula? what would you like in cells b1 through b3?

Hello. Thanks for replying. It can be any formula. Let's say =h1*h3.

 

[Danilo Montoni]

Try this:

Sub vzorec()
radek = 1 'number of row where could start formulas
Do Until Cells(radek, 1).Value <> "CAR"
Cells(radek, 2).Formula = "=LEN(RC[-1])" 'the formula is up to you
radek = radek + 1
Loop
End Sub

Hello Dura 1. thanks for your reply, but It didn't work for me.

Just to make more clear, i'm currently using the code below. But, as i said before, the number of rows with the imput CAR and GLO varies from month to month. So every month i have to make the adjustments manually.

Range("B1").Select
ActiveCell.FormulaR1C1= _
"=ROUND((SUMIFS(Ponto_Car!C[25],Ponto_Car!C[-8],R1C2,Ponto_Car!C[-6],RC[-7])+SUMIFS(Ponto_Car!C[26],Ponto_Car!C[-8],R1C2,Ponto_Car!C[-6],RC[-7])

Selection.AutoFill Destination:=Range("B1:B3"), Type:=xlFillDefault

Many Thanks!

 

[dura1]

Hi, i think If there are first cells with "CAR" in the column A and below them cells with "GLO" the code is OK. You have to check if the name "CAR" in cells and the "CAR" in code is exactly the same. Or i dont understand the problem.

 

[Danilo Montoni]

Hi, i think If there are first cells with "CAR" in the column A and below them cells with "GLO" the code is OK. You have to check if the name "CAR" in cells and the "CAR" in code is exactly the same. Or i dont understand the problem.

Hi. Many thanks for your reply. Actually, on my original sheet, the names CAR and GLO are on column D. And the fórmula should start on column E, row 4 (cell E4). How does your code recognize that the "names" are on column D and the fórmula must be placed on cell E4 and then "dragged"/filled from E5 to E6 (for example)?

___D_____E_________

4 CAR =formula
5 CAR
6 CAR
7 GLO
8 GLO
9 GLO

I changed the radek = 4 (becouse formula starts on 4th row (cell E4)) Then i and ran the code but nothing happens.

Sub vzorec()
radek = 4 'number of row where could start formulas
Do Until Cells(radek, 1).Value <> "CAR"
Cells(radek, 2).Formula = "=LEN(RC[-1])" 'the formula is up to you
radek = radek + 1
Loop

 

[dura1]

hi,if the column change, the code could by like this:
Sub vzorec()
radek = 4 'number of row where could start formulas
sloupec = 4 'number of column where are the names CAR and GLO
Do Until Cells(radek, sloupec).Value <> "CAR"
Cells(radek, sloupec + 1).Formula = "=LEN(RC[-1])" 'the formula is up to you
radek = radek + 1
Loop
End Sub

Hi. Many thanks for your reply. Actually, on my original sheet, the names CAR and GLO are on column D. And the fórmula should start on column E, row 4 (cell E4). How does your code recognize that the "names" are on column D and the fórmula must be placed on cell E4 and then "dragged"/filled from E5 to E6 (for example)?

___D_____E_________

4 CAR =formula
5 CAR
6 CAR
7 GLO
8 GLO
9 GLO

I changed the radek = 4 (becouse formula starts on 4th row (cell E4)) Then i and ran the code but nothing happens.

Sub vzorec()
radek = 4 'number of row where could start formulas
Do Until Cells(radek, 1).Value <> "CAR"
Cells(radek, 2).Formula = "=LEN(RC[-1])" 'the formula is up to you
radek = radek + 1
Loop