Filling down for as long as cells in another column are full 1

[keywordmonkey]

Hi

I've been trying to write code that will fill down a formula in F1. It needs to fill down for the same number of cells that have values in Column C. So if there were values in C1-C256, the formula would automatically fill down in F1-F256.

I know it needs an IF argument but I don't have enough experience to do this in VBA.

Cheers for the help.

 

[Zathras]

There are several ways to do it. Here is one way which I think is easiest to follow and may help you with future coding issues:

Option Explicit

Sub Demo()
  CopyDown "F1", "C1"
End Sub

Sub CopyDown(CopyWhat As String, CopyDownWhat As String)
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nCol As Integer
Dim rngCopyTo As Range

  nFirstRow = Range(CopyDownWhat).Row + 1
  nLastRow = Range(CopyDownWhat).End(xlDown).Row
  nCol = Range(CopyWhat).Column
  Set rngCopyTo = Range(Cells(nFirstRow, nCol), Cells(nLastRow, nCol))
  Range(CopyWhat).Copy rngCopyTo
  Set rngCopyTo = Nothing
  
End Sub

 

[HobbitK]

Zathras ..
Great coding! for that you get a star
If I might make one remark however, it would be since Keywordmonkey proclaimed limited knowledge in VBA, they would have learned a great deal more if you remarked your code to show what you are doing and perhaps why.
Just answering a question does little to elevate someones understanding, where explanations of code helps expand someones knowledge base.

 

[bryanbayfield]

In Excel, click on column C, press Ctrl+Arrow Down, now press the arrow right button until you get to column F. Enter anything in the cell you get to in column F.

Now press Ctrl+Up arrow to get into F1. Assuming your formula is already in F1, place your cursor over the bottom-right corner of the cell to get the cross-hairs. Now double click to fill down.

No need for VBA, that would be to emulate existing functionality in Excel, which is bad coding in my book.

 

[Zathras]

keywordmonkey: Brian is quite correct if all you are trying to do is give a user that functionality. Just train the user.

I took the other view that since this is the VBA forum, you were asking how to include that functionality inside some macro code that you are writing.

HobbitK is also quite correct in that macro code should be commented. I find that if I don't comment my code, when I return to it in 6 months to make the inevitable changes, it can take a bit of time to figure out what the heck I was doing. In your case however, I expected you to go to the help file to find out what the various bits do as part if the learning exercise.

 

[bryanbayfield]

I see what you're saying Zathras, but my philosophy is to make my life as uncomplicated as possible.

I guess if there was more detail in the request for assistance, we'd know which approach was best ...

 

[keywordmonkey]

Thanks everyone for the help .

In this case the code was the most useful as I need to add it into a macro I hava managed to record and edit myself - this part was the stumbling block (I should have said).

Ref. commenting code, it certainly does help me learn; I've been commenting every operation in my macros to help me learn and for change control in the future.

Cheers again!