VBA code - Drag down formula if a cell has a value in it.

[AshK12]

Hello I am trying to create a vba code that if there is a value or text in a specific cell (Starting with C100 to C105) then it would drag down the entire formula in one row to the next cell thats been populated until no cells in that range has value. This is what i have currently, i feel like i need to make thes range more dynamic based on the range in the variable X but not really sure how to do it. appreciate all of the help!

Sub Test ()
Dim x As Range
For Each x In Range ("C100:C105")
If IsEmpty (x) Then
Else
Range("D99:WP99").Select
Selection.AutoFill Destination:=Range("D99:WP100"), Type:=xlFillDefault
Range("D99:WP100").Select
End If
Next x
End Sub

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Sub Test()
  Dim x As Range
  For Each x In Range("C100:C105")
    If Not IsEmpty(x) Then
      Range("D99:WP99").Copy Range("D" & x.Row)
    End If
  Next x
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[AshK12]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try this:

Sub Test()
  Dim x As Range
  For Each x In Range("C100:C105")
    If Not IsEmpty(x) Then
      Range("D99:WP99").Copy Range("D" & x.Row)
    End If
  Next x
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hi Dante, much appreciate the warm welcome and thank you for replying!

For some reason the code does not really work or do anything.

The code should check if C100 and if its not populated with anything then nothing needs to be done. If C100 is populated with a value or text then the entire C100 to WP100 row needs to be populated with different formulas from C99 to WP99. Then code should go back and check C100 to see if its populated with a text or value and repeat the step. Hope this makes sense, ive been learning how to code/vba just couple days ago. I am a beginner.

 

[AshK12]

Hi Dante, much appreciate the warm welcome and thank you for replying!

For some reason the code does not really work or do anything.

The code should check if C100 and if its not populated with anything then nothing needs to be done. If C100 is populated with a value or text then the entire C100 to WP100 row needs to be populated with different formulas from C99 to WP99. Then code should go back and check C101 to see if its populated with a text or value and repeat the step. Hope this makes sense, ive been learning how to code/vba just couple days ago. I am a beginner.

 

[DanteAmor]

The macro does this.
If cell C100 has something then copy the formulas that are in row 99 and paste them in row 100. Then check cell C101 and so on to cell C105.

Could you put an image of what you have in cells C100 to C105, something like this:

In my image you can see that cells C100, C102 and C104 have data, so the macro puts formulas in that line.

Try again with this macro:

Sub Test()
  Dim x As Range
  For Each x In Range("C100:C105")
    If x <> "" Then
      Range("D99:WP99").Copy Range("D" & x.Row)
    End If
  Next x
End Sub

Don't forget to put your image.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[AshK12]

The macro does this.
If cell C100 has something then copy the formulas that are in row 99 and paste them in row 100. Then check cell C101 and so on to cell C105.

Could you put an image of what you have in cells C100 to C105, something like this:

View attachment 92173


In my image you can see that cells C100, C102 and C104 have data, so the macro puts formulas in that line.

Try again with this macro:

Sub Test()
  Dim x As Range
  For Each x In Range("C100:C105")
    If x <> "" Then
      Range("D99:WP99").Copy Range("D" & x.Row)
    End If
  Next x
End Sub

Don't forget to put your image.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

Thank you Dante, appreciate it!