0

I have some VBA code written which prepares a csv file for me so I can run individual macros on it for each model I have. The issue is every now and then, columns have duplicate data all the way down, rather than results of the formula copied down.

At first I thought it must be a memory issue, as if I rebooted and ran it again, it performed perfectly. This morning, regardless of how many times I rebooted, even once booting into Safe Mode on my Mac, it still did the same errors in the same columns.

What appears to happen on those times when it doesn't work correctly is in the CopyFormulaDownToLastRowOfData macro. It should be copying the results of the formula all the way down; so the results in each cell should be the result for that cell, but instead, it ends up with the result of the first cell copied all the way down. Here is the code for that particular macro

Sub CopyFormulaDownToLastRowOfData()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  With Range(ActiveCell, Cells(LastRow, ActiveCell.Column))
    .FillDown
    .Value = .Value
  End With
End Sub

Can anyone see any issues with it?

Thanks in advance

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
honkin
  • 113
  • 2
  • 12
  • 2
    Don't use `ActiveCell`... – BigBen Mar 04 '20 at 03:34
  • 1
    No need to use FillDown. You can enter the formula in the entire range in 1 go. `Range("A1:A" & LastRow).Formula = Range("A1").Formula` Use this method. If you want to start at row 4 then use `Range("A4:A" & LastRow).Formula = Range("A4").Formula` For finding last row, use it correctly as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) – Siddharth Rout Mar 04 '20 at 03:41
  • https://stackoverflow.com/questions/25788750/fill-formula-down-till-last-row-in-column – Siddharth Rout Mar 04 '20 at 03:45
  • https://stackoverflow.com/questions/59945293/dynamic-autofill-destination/59945614#59945614 – Siddharth Rout Mar 04 '20 at 03:48
  • My issue is the macro is run 4 times in the larger VBA code, so to actually specify a column like you have, doesn't really solve the issue. The VBA code selects the 2nd cell in whichever column needs to to be calculated and then this macro copies to the last cell. As an example, one part of the VBA code does column C with this code `Range("C2").Select Application.Run "PERSONAL.XLSB!CopyFormulaDownToLastRowOfData"` Other parts of the VBA code select different cells, so I need the macro to work on the selected cell. Does that make sense at all? Thanks again for your reply – honkin Mar 04 '20 at 04:20
  • `so to actually specify a column like you have` That is just an example. `"A1:A"` can also be written as `"A" & "1" & ":" & "A"` and then you can get the relevant column name from column number as shown [here](https://stackoverflow.com/questions/10106465/excel-column-number-from-column-name/10107264#10107264) so that `"A" & "1" & ":" & "A"` becomes `ColName & "1" & ":" & ColName`. Incorporating this in your code `Range(ColName & StartRowNumber & ":" & ColName & LastRow).Formula = Range(ColName & StartRowNumber).Formula` – Siddharth Rout Mar 04 '20 at 07:29
  • There is no code in the larger VBA code for this. The code simply calls the CopyFormulaDownToLastRowOfData macro, which I posted above. So at each of the 4 times it is required, it simply calls that macro and it works 99.9% of the time. For some reason, only every now and then, it does what I indicated above. So how would I rewrite this to do what I require, filling the formula results down to the last cell of data? Let's assume cell C2 `Range("C2").Select Application.Run "PERSONAL.XLSB!CopyFormulaDownToLastRowOfData"` Regards – honkin Mar 04 '20 at 08:11
  • @siddharth-rout Here is an example of what the VBA has currently `Range("V2").Select ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]=""X"",RC[-1]=""""),""*"","""")" Range("V2").Select Application.Run "PERSONAL.XLSB!CopyFormulaDownToLastRowOfData"` Would this be what would work instead of calling that macro? `Range("V2:V" & LastRow).Formula = "=IF(AND(RC[-2]=""X"",RC[-1]=""""),""*"","""")"` Cheers – honkin Mar 04 '20 at 09:50
  • `Range("V2").FormulaR1C1 = "=IF(AND(RC[-2]=""X"",RC[-1]=""""),""*"","""")"` and then `Range("V2:V" & LastRow).Formula = Range("V2").Formula` – Siddharth Rout Mar 04 '20 at 09:55
  • Thanks so much @siddarth-rout. Unfortunately that brings up a run-time error 1004 Method 'Range' of object _Global' failed. The formula is pasted into V2, but the debugger stops on the 2nd line of the code I am on Excel for Mac if that makes a difference – honkin Mar 04 '20 at 10:15
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/209036/discussion-between-honkin-and-siddharth-rout). – honkin Mar 05 '20 at 03:43

0 Answers0